TM1 Forum

Hi,

I have built a Forecast cube which holds budget and actual costs by month. The Measures dim has Budget, Actual, Variance and Forecast. The variance calculates the variance between budget and actual costs. I do not want the variance calculation to be part of the hierarchy because I only want a variance to be displayed for either the Current month or historic months (if part of the hierarchy it will always display a variance in forcast months.

(This is because I have a rule that takes the sum of the variance (current month and historic) and spreads it back over the remaining forecast months so that forecast costs of the "Project" will always come back to teh same value of the original budget. )

I have written the following rule:

# The following code puts the delta (Original Budget - VOWD) into the dummy Vendor and PO for Original Budget:['OB_VOWD','Original Budget Vendor','Original Budget PO']=N:
IF(!Tullow_Measures@=DB('Current Month','aNumber','aString'),
(['Original Budget','Original Budget Vendor','Original Budget PO']-['Value of Work Done','TOTAL VENDORS','TOTAL PO']),Continue);

I have a "Current Month" cube in which I can input the current month. At the moment this cube says "Oct-11". My Forecast cube correctly calculates the variance between Actual and Budget for Oct-11 which I am happy with. However, To get the variance calculation to function for the current month AND all historic months I changed the "@=" to "@<=". This does not have the required effect! It performs the variance calculation in multiple random months (both historic and future).

I wondered if this was because the dim comparison was comparing an index number of the element rather than "Oct-11". However that doesn't seem to make sense either.

As always, any ideas would be much appreciated.

Many thanks

You are doing a string comparison, that's what the @ is doing.

Therefore Dec-11 is true for the condition @<= 'Oct-11' since D is before O in the alphabet. For that matter Dec-12 would also fulfill this condition.
Whereas Sep-11 is false since S is after O in the alphabet

The months that your rule is calculating for are not random, it is just you are testing the wrong thing!

What you need to test is the DIMIX of you months (assuming the months are all in index order with no hierarchies corrupting the order). But what would be better would be to have a numeric attribute for the month order or index values and test that as well since this won't be impacted by dimension structure.

You can then also adjust the rule to set the variance to 0 or #N/A for future months as well. Variance does not need to "be outside the hierarchy" as you can control the calculation by month in the rule.

Lotsaram,

Thanks for your reply. What you say re the string comparison and the letters of the alphabet makes perfect sence and it's not random at all (the clues in the name really "String Comparison"...I should have picked up on that)!

I have ammended my rule as follows:

# The following code puts the delta (Original Budget - VOWD) into the dummy Vendor and PO for Original Budget:['OB_VOWD','Original Budget Vendor','Original Budget PO']=N:
IF(DIMIX('Forecast Measures_Measures',!Forecast_Measures)<=DIMIX('Forecast_Measures',DB('Current Month','aNumber','aString')),(['Original Budget','Original Budget Vendor','Original Budget PO']-['Value of Work Done','TOTAL VENDORS','TOTAL PO']),Continue);

This works fine - does exactly what I want.

However, your point about using a rule to set the variance to "0" for future months is a good one and I hadn't considered that. I wonder if that would be more efficient than my rule above as the initial calculation would sit in the dim hierarchy? My worry is that the cube has 3 other big dims (project codes, Vendor codes and PO numbers), therefore I want to calc as efficiently as possible.

I may give this a try and post a reply later.

Thanks again for your help - I would be pulling my hair out of it wasn't for this forum!

Cheers

I'm not a big fan of using DIMIX for stuff like this because it can be broken too easily. Someone new takes over the model, changes the dimension order, and you're toast. A specifically spelled out order in the form of an index number attribute is safer, IMO. It's one of the attributes I always put in all of my time dimensions.

Tomok,

thanks for the advice. Originally I thought that given I am DIMIX on a time dimesnions it should be pretty safe as future users would be unlikely to ammend the element order fo a time dimension - but you are right it would be much safer to work of a order number attribute. I will implement it this way.

Thanks again.