Hi,
I am using Cognos Report Studio 8.4 to extract the sales data for 2011.
The data should be pulled out like:
Product Category … YTD Count
A *
B *
C *
Here, [YTD Count] should be the summation of distinct counts of a specific product category sold across all 4 quarters in 2011, i.e.:
"[YTD Count] for A" = “Distinct count for A category products sold in Q1” + “Distinct count for A category products sold in Q2” + “Distinct count for A category products sold in Q3” + “Distinct count for A category products sold in Q4”
For achieving this goal, I used 2 steps:
1). Used the following expression for [YTD Count]
Case
when
([Deal Dimension].[Quarter Code] in ('1', '2', '3', '4'))
then (count (distinct [Product Dimension].[Product Number] for [Deal Dimension].[Quarter Code]))
else 0
end
2). Used the aggregation property as Total
But, the results I got for each product category (i.e. A, B, and C) are all the same as the total count for the 3 product categories.
For example, I should get:
Product Category … YTD Count
A 180
B 170
C 210
But, I got:
Product Category … YTD Count
A 560
B 560
C 560
Here, 560 = (180 + 170 + 210)
How to deal with this problem and get the count values by product types across 4 quarters in 2011?
Thank you in advance.