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