Growth comparison calculation against a previous period.

[#1]

Let me set the scene....I've got a Sales Fact table with the standard Customer / Product / date etc fields and a related Calendar table with year / qtr / Month / date etc that describes our specific company fiscal calendar.

*The sales fact tables contains the date at the lowest level - 1st Jan 2011 / 2nd Jan 2011 etc.
* I can use the calendar table to aggregate the sales data by whatever period I have (Year / Qtr / Month)

But the challenge I put forward is I'd like to create a growth comparison calculation that takes the total month sales and compare it to the same month last year to determine the difference which I want to plot on a graph by month.

Any help would be useful

Cheers, Jay

[Anonymous · Accepted solution]

Hi Jay,

I have done this by using the month as the dimension and using these expressions:

Sum({$<Year={$(=Max(Year))}>} sales)

This will return the sales for the highest year within your selection only

and for the next expression:

Sum({$<Year={$(=Max(Year)-1)}>} sales)

This will return the sales for the year before the highest year within your selection only

Let me know if this is what you are looking for.

[#3]

Dennis - great response and works a treat!

Cheers, Jay

[#4]

I've got another question....on similar lines.

I want to create a bar chart of sales by month with each bar representing the sum of the last 3 months. For example in the chart below, I'd like 2011-Feb to sum the sales of 2011-Feb / 2011-Jan and 2010-Dec
2011-Jan to sum the sales of 2011-Jan / 2010 Dec and 2010-Nov
etc...
etc...

Any Ideas?

Thanks, Jay

[Anonymous]

Hi J.

You can do this the same way but you can also work around this bij just ading the previous expresion to a new expression. I think it is a funny way to do this 🙂

This is how it works:

Dimension: MonthYear

Expression 1:
- Label : namelabel1
- Defination : sum(sales)

Expression 2:
- Label : labelname2
- Defination : sum(sales) +Above(namelabel1)

Expression 3:
- Label : labelname3
- Defination : sum(sales) +Above(namelabel2)

If you make Expression1 and 2 invisiable (QV10 only) it will only show Expression 3.

Down side of this is that the first months in your selection doesn't have an "above" but maybe it is helpful for you anyway.