% change from same metric in different year

[HTCMS]

I need to do a % change metric from the same metric but different time period. Currently, I've a attribute - country and Year, a metric - count.

E.g.
Year 2003 2004
Country Metric Count Count
U.S. 100 20
German 140 50
Japan 110 25

Need a metric to do something like below:

Year 2003 2004 %Change
Country Metric Count Count
U.S. 100 20 -25% (Here's how I get the -25%: (20/3) - (100/12) = -1.67 -> (1.67/6.66)*100)
German 140 50 30% ((50/3)-(140/12) = 5 -> (5/16.67)*100)
Japan 110 25 -9.96%


Any comments and suggestion are appreciated. Thanks alot.

 

[Z3]

You can use OLAP Services - this should be an easyone.
If you need the dynamic month numbers, you can create a metric that returns how many months are avail in this year so far, then sill uses the OLAP Service.

 

[HTCMS]

I'm kinda new to MSTR, for the OLAP service, are you referring to those OLAP functions, like applysimple, applycomparison? Thanks again for your help.

 

[wolf5370]

You can use Transformation to transform your "Count" to Metric "Count Previous Year".

Then you have the ability to use a compound metric - ("Count"/"Count Previous Year")*100. In column 3.

This is assuming you always want Year on Year (E.g: Run for 2003 - you would get 2003, 2002, %Yr 03 to 02 - run for 2002 you'd get 2002,2001, %yr 02 to 01).

If you want any years then you could prompt for a date in each of the two metrics that make up the compound.

 

[HTCMS]

Hi,

If I use the count Previous year, will the transformation roll up the number for the whole year? I'm not too sure if I can use the transformation, cux it's not exactly year on year. Like if you want to compare the number for 2003 and 2004, 2004 has only 4 months data while 2003 got 12 months, so I will need to average the count for 12(year 2003) and 4(year 2004). I dunno if the count with tranformation will do so?

 

[wolf5370]

You use a transformation table to do the transformation - therefore, it can cater for week on week by year etc without having to match up - you do not need to use dynamic dates, you can use your own date tables. For example, we have the same issue - Mothers day does not match up week on week over the year, so use a tx table to transform that week to another regardless of the true this week last year.

It you transform week ending then the 4 months you set for this year (so far) will match up on week ending transformation on a week to week basis to last year - i.e. the first 4 moths of last year - this usually is better than an average as averages take out seasonal peakss (xmass, easter, mums day, annual sales etc).

If you want 4 month against the average of 12 months then this is possible to - but may not be very useable.

(Sorry for any typos - been to a leaving do - under the affluance of inkerhol - or something like that )