LOOKUP on Time | Model Optimization Team series

modeloptimizationteam
modeloptimizationteam
edited December 23 in Best Practices
ModelOptimizationTeam.png

Hello Anaplan Community! Welcome to the Model Optimization team's monthly blog post. This is our last post in an eight-part series, on the topics we see occurring most frequently in slow customer models. Our aim is to inform and guide how to build with best practice intentions for these model building topics.

Each month, we take you through how to spot and improve these formula structures! We hope these can help with your model performance.

All the best - Cianna Bramley (Model Optimization Manager)

……

This month's blog is brought to you by Ara May Baral and Mai Hashad.

Dec-authors.png

In this month’s blog post, we’re going to tackle one of the common functions used in Anaplan, the [LOOKUP:] function, and why it’s bad when we use this function to reference Time. We’re also going to discuss how we can improve this LOOKUP on time, specifically, using better alternatives such as YEARVALUE, MONTHVALUE, etc.

Why can it be bad?

To generate the value of a data from a certain timescale, some model builders use a LOOKUP function. For example, if they want to pull a value from a Year dimensioned module to a module with Month timescale, they may be thinking to use LOOKUP to look up its value in its equivalent Year. However, using LOOKUP on a timescale may be calculation intensive under certain conditions as the function requires extra work to do relationship-mapping to pull the data through. Especially if we use LOOKUP on a larger time scale, let’s say 5 years or higher, more combinations must be checked and must be satisfied.

In comparison, functions like YEARVALUE(), HALFYEARVALUE(), QUARTERVALUE(), MONTHVALUE(), and WEEKVALUE() are dedicated functions to check an equivalent value of a certain timescale. We can think of these specific functions as functions that gather the parent of a certain timescale (e.g. Year as a parent of Month or Week).
In addition, other functions such as OFFSET, LAG and PREVIOUS can also be used to check the value from the past or future time periods. These functions are much faster than [LOOKUP:] since they are dedicated functions to calculate values related to time.

In 2019, there were some comparison tests conducted to compare LOOKUP with other functions. From this, we can see that LOOKUP is slower when compared to other functions, which is more evident as the number of time periods increases. To learn further about this test, here’s the link.

How do we find it?

  1. Through manual inspection: upon extracting the line items from your model, filter the formulas that uses LOOKUP in line items applied to time scale. Check whether the LOOKUP mapping is formatted as a Time Period (e.g. [LOOKUP: SYS01 Time Settings.Year]);
  2. As part of Model Optimization report: in our Model Optimization reports, we discuss Best Practices for model building, which includes replacing LOOKUP on time.

How do we improve it?

In this section, we will discuss several examples on how to replace LOOKUP on Time in different scenarios.

Example 1

Picture1.png

In blueprint view:

Picture2.png

This line item uses Month timescale. We want to gather the quarter time summary of 'REV02 Volume Inputs - Copy'.Volumes; however, it uses a LOOKUP function to look up the Quarter equivalent of the Month timescale. Instead, we can replace this LOOKUP with a QUARTERVALUE() function.

The line item becomes:

Picture3.png

Example 2

Picture4.png

In blueprint view:

Picture5.png

This line item uses a combination of a SUM and LOOKUP functions, which is not a good practice when building a model. As discussed in one of the Model Optimization articles on combining SUM and LOOKUP, we can create an intermediate line item to calculate the SUM first, then reference this intermediate line item by using LOOKUP. However, if we check further, the LOOKUP references a line item in a system module with a Year format. With this, it is better to use YEARVALUE function than LOOKUP.

Follow the steps below:

  1. Create an intermediate line item:
    Picture6.png
    In this example, the source line item (Revenue) is also dimensioned by Month timescale. As we wish to pull the Year value of the data, the intermediate line item needs the SUM summary aggregation so that the Year values are populated .
  2. Replace the LOOKUP in the original line item with YEARVALUE referencing the intermediate line item.
    The line item becomes:
    Picture7.png

Example 3

Picture8.png

In blueprint view:

Picture9.png

In this example, the line item wants to get the value of Revenue from the previous month using LOOKUP. Since we want to get previous values, we can utilize the PREVIOUS() function, as this function performs faster as compared to LOOKUP.

Follow the steps below:

  1. Create an intermediate line item:
    Picture10.png
    In this intermediate line item, we can calculate the Revenue value, excluding the LOOKUP on the previous month. The summary of this line item can be set to ‘NONE’ as it doesn’t need to pull the value at the parent hierarchy, unlike the other functions used in the earlier example.
  2. Reference the intermediate line item created to the original line item, and use PREVIOUS().
    The line item becomes:
    Picture11.png

Example 4

Picture12.png

In blueprint view:

Picture13.png

In this example, we need to get the Revenue value from the corresponding month in the previous year (Month timescale).

One scenario is if we are looking at the Revenue value on Dec 24, we will need to get the Revenue value from Dec 23. Rather than using LOOKUP, we can utilize using the OFFSET function.

The line item becomes:

Picture14.png

Summary

To reference a certain timescale or another period, we can utilize Anaplan functions intended to be used to calculate values related to Time (e.g. YEARVALUE, MONTHVALUE, OFFSET, PREVIOUS). These dedicated functions perform much faster as compared to using the LOOKUP function; therefore, using these functions will help your Anaplan models to perform more efficiently.

……….

Check out the other articles in the series:

Tagged:

Categories