How to Dynamically Assign Information based on Data Entry

Have you ever wondered how to have a flat list in TM1 and then send values off to different elements on various dimensions depending on what is entered in the list? For example, you have a list of people that you are budgeting for and next to each person you have their salary etc plus the cost centre they are assigned to. The cube works dynamically and sends the costs and other measures or calculations to the required periods and cost centre, from where they can then be included in overall plans for the business.

Where to Enter Data

At Infocube we use a system we call the “No’s”. Building on from the example above, we would enter the labor details for every person in the company into a period called “No Period”, a cost centre called “No Cost Centre”, for the Forecast version. Importantly the “No’s” are always built outside the primary rollup structure so we never end up double counting. We would then enter the detail against each EmployeeID and have measures for the Cost Centre, Start Date, and Salary.

This way we can see all employees in a flat list AND can change the assigned cost centre or start date directly in the list.

This makes it very easy for the user to complete the exercise.

Below shows the initial position, with one person each on cost centres 1 and 4 and three on cost centre 3.

We then change employee 3 to be assigned to cost centre 2 and immediately the data is moved to this new location.

Rules

Basic Rule to Assign Data

The rules to push the salary costs out to the required cost centre are quite easy. All that is needed is:

['Budget','Salary'] = N:  
     IF ( !Cost Centre @= DB('Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Cost Centre') 
         ,['No Period','No Cost Centre','Budget','Salary']12
         , 0 );

Using the IF and DB functions, what this does is says the budget salary (for any period, for any employee for any cost centre) is equal to either the salary/12 or zero, depending on if the cost centre being assessed is equal to the cost centre referred to in the “No’s” or not.

More Complex Rule – Start Date Assessment

What the rule above does not do though, is use the start date. What we need the rule to do is check if a start date for an employee exists and if so, whether the period being assessed is greater than or equal to the start date entered. If so, then do the calculation of salary/12.

What makes this more complex is that the date entered is a serial date (so a number) and the period being assessed is a period in the form “Nov-19”. In this we are going to use the IF and DB again, as well as Year and Date functions. Here is what it looks like:

['Budget','Salary'] = N: 
     IF ( DB('Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Start Date') = 0
         , IF ( !Cost Centre @= DB('Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Cost Centre') 
             , ['No Period','No Cost Centre','Budget','Salary']12
             , 0
         )
         ,IF ( ATTRN ( 'Month-Year' , !Month-Year , 'MonthNo' ) >=   Year ( Date ( DB ( 'Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Start Date' ) , 1  ) ) * 100 +  Month ( Date (  DB ( 'Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Start Date' ) , 1 ) )
             , IF ( !Cost Centre @= DB('Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Cost Centre') 
                 , ['No Period','No Cost Centre','Budget','Salary']12
                 , 0
             )
             , 0
         )
     );

What this rule literally does is first check if the start date (a value) is equal to zero. If it is, then we do the logic from the basic rule above. If the start date is not zero, then we need to compare the period being assessed to the start date and if it’s in the past, then do the basic calculation, and if not, then zero. There are a couple of tricks in this though:

• We use a numeric attribute on the Month-Year dimension in the form YYYYMM, so for example, the number 201911 for November 2019.
• We then get the start date using a DB from the “No’s”. This is a serial number (like 43912).
• We then use the Date function on the serial date, to convert it into a string in the form YYYY-MM-DD.
• From there we get the Year as a value, 2019, and multiply it by 100 to get 201900.
• Onto that, we add the Month value of 11 which equals 201911.
• This is directly comparable to the numeric attribute of 201911.
• Easy peasy!

Feeders

The feeders are also quite easy when you know what to do. At Infocube, we like to build rules and feeders up from a basic start to more complex, rather than just trying to nail a complex rule or feeder off the bat. Therefore initially we set the feeder up simply to feed into the first month of the Month Year dimension and the hardcoded Cost Centre 1, just to prove that it works. This looked like this:

['No Period','No Cost Centre','Budget','Cost Centre'] => DB('Labour Plan','Jan-19','Cost Centre 1',!Version,!Employee,'Salary');

Here we are feeding the Cost Centre measure from the “No’s” to the Salary measure for Jan-19 and Cost Centre 1.

Note here that we are feeding from a string to the number. This way the user can change the cost centre and the feeder will re-fire to the newly selected cost centre. One of the odd things about TM1/Planning Analytics is that a feeder based on numeric value will only trigger once, but a feeder based on a string will be run whenever the source changes. You also need to have FEEDSTRINGS enabled at the beginning of your rules sheet.

We can then modify the feeder above to target “All Years” (with all months as the N level elements underneath All Years, so they are therefore fed) and insert a DB to feed the Cost Centre selected by our user on the “No’s”. The final feeder will look like this:

['No Period','No Cost Centre','Budget','Cost Centre'] => DB('Labour Plan','All Years',  DB('Labour Plan','No Period','No Cost Centre','Budget',!Employee,'Cost Centre'),!Version,!Employee,'Salary');

Where Could You Use This Technique?

We have used this technique for years. Some of the examples are, obviously in labour planning, like we have done above. We’ve also used it for things like capex planning or motor vehicle expense planning. Basically anywhere where you want to do planning one a single location and dynamically assign values to elements in one or multiple dimensions.