Calculating “As At” Values in a TM1 Rule

Have you ever needed to calculate the value “as at” a point in time? For example, you have an inventory cube and want to show the volume of stock at the end of a quarter, or end of the year. You might have values in each month, but if you use a regular consolidation for months, it won’t work for you. You will end up with each month aggregated and a total for the 12 months, rather than the last value.

Calculating As At Values

In the example below we have a continuous time dimension. we have values for FTE and Average Annual Salary for each week. We needed our rule to populate the month value with the value for the last week.

To do this we created the following rule:

['FTE'] = C: DB('Labour Plan', ELCOMP( 'Time', !Time, ELCOMPN( 'Time', !Time)), !Version, !Department , !Location, !Labour Role, !Measures - Labour Plan);

What this is saying is the measure FTE at a consolidation level is equal to the last child element of a parent. In other words, put the as at value into the parent!

Note that we have not (yet) calculated the rule for the Annual Base Salary. So this is showing $440,000 for the first period, which is somewhat more than a person loading trucks is likely to earn!

Explanation of the Rule

So, for example we have the following structure:

• 2021
  • 2021 Period 1
    • 2021 Week 1
    • 2021 Week 2
    • 2021 Week 3
    • 2021 Week 4

When evaluating 2021 Period 1, the ELCOMPN returns 4 children. Then ELCOMP then goes and gets the 4th element from the structure, which in this case is 2021 Week 4. Therefore the value for 2021 Period 1 will equal the contents of 2021 Week 4.

If, alternatively, we had the following:

• 2021
  • 2021 Period 1
  • 2021 Period 2
  • 2021 Period n
  • 2021 Period 12

Then, assuming there were 12 periods, the ELCOMPN would return 12 and the twelfth period is 2021 Period 12. Therefore the as at value would be the number in period 12.

Pretty clever, huh!