How to get the count values by product categories across 4 quarters?

[lookingforK]

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]

Code Select Expand

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.

[blom0344]

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?

[lookingforK]

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...

[Lynn]

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?

[lookingforK]

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