TM1 Forum

Hi

This is my first post and I am a new user to TM1, so apologies in advance if this isnt the clearest way of asking!

I am having a bit of trouble writing a rule for calculating sales per working days, where the working days can vary from country to country or comapny to company (all in my Company dimension). I can calculate it at N: level for individual months, but for the consolidated months (such as YTD, Half Year, Quarter Year) I cant get it to consolidate in the way Id like. Screen shot below should hopefully explain:



For 1601, the SPWD is consolidating up the company hierarcy, so when at the top level it is correctly showing: 511+450+1707+2661+1641=6969 rather than 137682/20 = 6884. I am happy with how this is working.

However, 1602_YTD is calculating 300493/41 = 7329 rather than the desired 519+488+1884+2710+1774 = 7375

I realise that this is entirely because of how I have currently written my rule. I just cant quite figure out how to write it so that it behaves how I want it to. Im probably (hoping) missing something very simple.

My current rule is:

['Sales per Working Day'] = N:['Total Sales'] \ ['WDAY','Value k'];

['Sales per Working Day'] = C:
IF(ELLEV('Month',!Month) > 0,
['Total Sales'] \ ['WDAY','Value k'],
Continue);

The reason for the second rule is that I didnt want it to accumulate the values when viewing YTD. I couldnt have a '=' type rule because the WDAY value at consolidated level is not always a true reflection of the child element WDAY values.

Is anybody able to point me in the right direction?

Many Thanks

if you want the SPWD accumulating on C: time elements, you may want to take a look at the ConsolidateChildren function

I would use it only on the Time dimension if the other rollups are ok as they are currently because it can significantly slow down rule calc performance

Great, thank you so much for the suggestion. I have tried consolidatechildren (on the company dim) when the month is greater than level 0 and I am now getting the desired result.

yes, sorry, Company dimension, just be sure you test with all other dims as well, I saw you have at least 2 other hierarchical dims in the cube?