Comaring current and prior year

[tizzyb]

Hi,

I want to create a report in Powerplay which compares the sales values for each month in the current year with the corresponding month in the prior year.
I am struggling!

I want to produce something that looks roughly like this

JAN FEB MAR APR etc

curr year 10 20 12 45

prior year 13 23 10 40


I need to show this for each of a list of products. So The report could instead show something like: products in columns and the dates somehow in the rows.

Please help!

Regards
Tiz

 

[drlex]

Tiz,
I think to do this, you need to have two date dimension, one with no year level and one with, so that the first can provide the month and the second can provide the year.
If it's mot in the model, you'll need to add it with transformer.
(If you use the date wizard, you will get some special date categories automatically generated, which include a YTD comparison (YTD group))
lex

 

[tizzyb]

Thanks Lex,

I get the theory. I've created my new time dimension with only months as a level. And I've told it to format the months as MMM rather than YYYY/MMM.
But it still insists on giving me a category for each month for each year.

Any ideas how I get it to group all Jans under one Jan?

Tiz

 

[drlex]

Tiz,
Yup - insert a new column in the data source, make it calculated and use the MONTH(<your date>) function or duplicate the <your date> field and use the &quot;time&quot; tab of the properties of the column to set M as the degree of detail.
lex

 

[tizzyb]

Great, that's done the trick!
Minor thing is the month is defined as a no 1-12 rather than a string.

Thanks for your help.
Tiz

 

[jyash]

hi
u can incoporate it by creating a new dimension in the cube...which u have to create the new dimension by inserting a calculated column....the calaculation should be month(date column)...then use this in ur report.....an example of this is given in step by step transformwner document....on pg 93.....hope that helps
yash