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:
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.
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).
Thank you pricter.
I have solved it.