Hi,
I am using Cognos 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.
totalizing the figures is done outside the scoped aggregation which will give you an overall figure like you are now getting.
Try to use another aggregate setting like 'calculated' . What happens then?
Thank you blom0344.
I used another aggregate setting like 'calculated', but it could not work.
What I got is like:
Product Category ...... YTD Count
A 120
B 110
C 98
A 112
B 102
C 95
...
...
Don't know what it is...
I'm not sure what the point of the case statement is in your expression. Is there such a thing as a 5th quarter? By definition quarters implies only 4.
I don't think that is your problem, however. I think the problem is possibly your scope which only references quarter without mentioning product. If you add product to the scope does it get you what you need?
Thank you Lynn.
I have solved this problem using 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], [Deal Dimension].[Product Category]))
else 0
end