How to extract data about RATE for 2012 Quarter 1, Quater 2, and Quarter 3?

[lookingforK]

Hi,

I am creating a list to extract data for a product using Cognos Report Studio 8.

The columns in the list are:
[Store]   [YTD Sales Rate]   [Quarter 1 Sales Rate]   [Quarter 2 Sales Rate]   [Quarter 3 Sales Rate]
.......
.......
.......

Sales Rate = (Sales Volume) / (Total Number of the product)
* For example, 2012 [YTD Sales Rate] = (Sales Volume to date in 2012) / (Total Number of the product to date in 2012)
* For another example, 2012 [Quarter 1 Sales Rate] = (Sales Volume in Q1 2012) / (Total Number of the product in Q1 2012)


I filtered the data with only 2012 months, e.g. Jan. ~ Sep in 2012.

I used the following expression for Quarter 1:

Code Select Expand

CASE
WHEN
([...].[Deal Dimension].[Month] in ('JAN', 'FEB', MAR'))
THEN ([...]. [Fact].[Sales Count] / ([[...]. [Fact].[Count]))
ELSE 0


I set its aggregation property as Total.

But, I only got the values of sales volume in Quarter 1. It looks like the Rate calculation did not work.

How should I do?

Should I create Data Items for each quarter?
For example, for Quarter 1, can I create 2 Data items (Q1 Sales Volume; Q1 Total Count) and then use the Rate calculation?


Thank you in advance.

[pricter]

Instead of using a list is it acceptable to use a crosstab?

Secondly what you have done results will be the total of the divisions by month

For example if you have the following data

Month Sales Count   Count   Calculated ited
Jan        100                10            10
Feb        50                  10            10

then for quarter one this will resuld in 20 (10+10) and not 7,5 (150/20).

 

[lookingforK]

Thank you pricter.

I have solved it.