Apply logic to a base year

Post Reply
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Apply logic to a base year

Post by Mark RMBC »

Hi all,

Just wanted to share this for 2 reasons,

Help other users if they are faced with this situation
and more to the point invite better and more efficient options!

Disclaimer!

Not every step has been included but I think I have covered enough to explain the issue (apologies if this turns out not to be the case!)

The background…

I was asked to build a staff budget setting model in TM1 which set the budget for the next 5 years. One of the requirements was to capture staff increments, which is moving from one salary point to the next. Within my organisation increments are not necessarily given annually, they could be bi annual or every three years etc depending on the Pay Body. So for example, teachers are annual, social workers bi annual. It should also be noted that an employee’s next increment date could be any day or month within the year!

The Problem…

How to apply the increment based on the frequency of the increment and the next increment date of the employee?

My Solution…

I approached this problem with the idea that a base year needed to be set and against this base some logic had to be applied which asked the question, does the year in question divided by the base year equal a whole number?

So the first thing was to create a numeric attribute against the Pay Body called increment frequency. If this was 1 then that meant the increment was annual, if 2 the increment bi annual and so on and so forth.

The next consideration was do I use a Rule, TI or some other method. I decided upon a Rule.

One of the elements in the Staff Budget Setting Measures dimension was the date of the next increment and I could use this date to apply the logic to. So for example, for employee X the date of the next increment could be 1st April 2017. Let us assume employee X is a social worker and receives their increment bi annually. This means that after 1st April 2017 the next increment is 1st April 2019 and then the next 1st April 2021 etc.

Now let us imagine we are setting the budget for the financial year (April to March) 2017/2018

We know the employee will receive an increment on 1st April 2017, if I take a substring of the year, so return 2017 and then take this away from the contextual !Year (dimension) we would have the sum:

2017 – 2017.
We could then divide this figure by the increment frequency, so we now have:

(2017 – 2017) / 2 = 0

If we moved to the next year, the sum would be:

(2018 – 2017) / 2 = 0.5

From the above formula we can see that whenever the above formula does not equal a whole number then this indicates the increment is not due in this year.

So the next step was to test if this was a whole number or not.

The way I did this was to take the above formula and take away from this the same formula but with the function INT included. So if we imagine the next year the formula becomes:

((2018 – 2017) / 2) – INT(((2018 – 2017) / 2)) = 0.5 and therefore no increment

If we take the next year the formula becomes:

((2019 – 2017) / 2) – INT(((2019 – 2017) / 2)) = 0 and therefore increment is due.

So if the result of this formula is 0 then increment due otherwise increment not due.


In the rule it looks something like (though other logic has been removed!):


['Increment Due']=S:

if(
((NUMBR(SUBST(!Year,1,4)) - numbr(subst(DB('Staffing-Budget-Setting',!Employee,!Job Number,!Cost Centre,!Detail Code,!Year,!Versions,'Salary Review Date'),7,4))) \ attrn('Pay Scales', DB('Staffing-Budget-Setting',!Employee,!Job Number,!Cost Centre,!Detail Code,!Year,!Versions,'Scale Code'),'Increment Freq'))
-
Int(((NUMBR(SUBST(!Year,1,4)) - numbr(subst(DB('Staffing-Budget-Setting',!Employee,!Job Number,!Cost Centre,!Detail Code,!Year,!Versions,'Salary Review Date'),7,4)))\attrn('Pay Scales',DB('Staffing-Budget-Setting',!Employee,!Job Number,!Cost Centre,!Detail Code,!Year,!Versions,'Scale Code'),'Increment Freq')))
= 0,
'Y',
Stet);
Post Reply