YTD difference - rule or TI?

[wigglyrat]

Hi - I am looking to create a measure, 'YTD Difference.' This involves two dimensions 'Year' and 'Month' so in order to find the current date (which will be current month) both will need to be used.

Is this possible to do this in a rule and if so how would you go about this?

Code: Select all

['YTD Difference']=['1','2016','Renewal','Written Commission %']-['1','2015','Renewal','Written Commission %'];

In this example, I have hard coded current year as '2016' and the current month as '1' but this is clearly poor practice.

If anyone can please provide an example of how to do this - instead of '2016,' get current year, instead of '1,' get current month, (and another question: would 2015 be 2015 minus 1, and if so what would that mean for 'January' 2014 (could you subtract 1 from this to get the value you want?) it would be greatly appreciated.

Many thanks in advance,

[jim wood]

You create a previous attribute for each year. You can then change the rule to a full DB rule and reference the previous year in the second part of the equation.

[lotsaram]

You can make your rule more generic and maintenance free by adding a "last year" and "next year" attribute to the year dimension. That would be better than what you have.

But the real question for this calculation is not rule vs. TI it should be rule vs. dimension consolidation. Any time you have a calculation that is mono-dimensional addition or subtraction the most performant calculation method is consolidation not rule. YoY variance is also a generic concept, it is no more exclusive to commission than it is to 2016 vs. 2015; it applies to all measures across all years. If you create a "2016 PY Var" consolidation with children 2016 with weight 1 and 2015 with weight -1 then you have solved the problem without rule or TI. Now repeat for all YoY pairs. Now it is a generic calculation for all measures not just commission and it is a generic calculation for all cubes where the year dimension is re-used with no overhead and no further maintenance.

[jim wood]

A very good point Lotsaram. I guess what would help answer the question either way (which I wasn't clear on via the first post. I did read first thing in the morning mind) is whether you need the YTD difference for every year or only the current year?

[paulsimon]

Hi

I few other points to mention.

I would avoid using '1' for a Period Name - best to use 'P01'. The reason being that as soon as you go out to Excel, any numeric code will get treated as a number.

To do the YTD Difference with separate Year and Period dimensions you will need the 2016 PY Var (2016 - 2015) consolidation that Lotsaram suggested. It perhaps was not clear that you will also need YTD consolidations in the Period dimension, eg P01_YTD is a consol of P01, P02_YTD is a consol of P01 and P02, etc. In that way you will can select the appropriate PY Var consolidation in Year and the appropriate YTD consolidation in Period to get the Year on Year YTD Difference.

If you are still at the early stages of designing your cube I would also recommend that you consider a single Year_Period dimension, as this will then allow you to do Cumulative to Date, Rolling 12 Months, Prior Period, etc, all just by consolidation. There are various ways of generating this.

Regards

Paul Simon

[wigglyrat]

I have tried consolidation and it worked perfectly. Thank you to everyone for your help.