1.07-02 Line Item subsets for Version Formula

rob_marshall

Use Line Item subsets to create different numeric formulae for each version to avoid multiple Ifs

Best Practice article:
Line Item Subsets Demystified
Decreasing the Length of Your Formulas
Variance Analysis With Native Versions Made Easy

Misbah

Rule 1.07-02 Version line item subsets. Use line item subsets to create different numeric formulae to avoid multiple ifs. It goes against Performance, Logical elements of PLANS if you wish to use multiple if statements.

Use Case: Based on the user defined months we want to compare the values for KPI metrics for selected months.  Metrics such as Revenue, Gross Profit, Salary, Operating Expenses and Non-Operating Expenses are calculated in different modules and share the common dimensionality (Country)

Note: Revenue and Gross Profit are calculated in Revenue Module

          Salary and Operating Expenses are calculated in Employee Expenses Module

         Non Operating Expenses are calculated in Other Expenses Module

Here is how it was done in Pre Planual Era:  

Source Modules: Revenue Module, Employee Expenses Module, Other Expenses Module, Select Months Module

We used to create a module with following artifacts

Dimensions of Target Module: Country, Users and Metrics

Line items: Month 1, Month 2 and Delta

Formula in Month 1

Formula in Month 2

What is wrong with this method? It is considered to be a very complex formula because Anaplan Engine has to check each condition to return the corresponding value – thus impacting the performance. Logically also it makes no sense to take the long route when you have a shorter route available.

Here is how it should be done in Planual Way:  Here is a step by step guide

Step 1: Create a Line item Subset based on Revenue, Employee Expenses and Other Expenses Modules. Enable all those line items in the LIS required here

Step 2: Create LIS staging module which will pull the information from all three source modules by using COLLECT() function. This module will be dimensioned by Country, LIS and Time

Step 3: Create a System mapping module dimensioned by Metrics List and insert a line item formatted as LIS

Step 4: Create a module dimensioned by Metrics, Country and Users. Insert three line items i.e., Month 1, Month 2 and Delta

 Formula in Month 1:

Formula in Month 2:

Note that there are two LOOKUPs in the formula. Although this is far better than IF ELSE statements it is still doing transformations before the output is given. So let’s try to get rid of one extra lookup

Step 4(a): Create a module dimensioned by LIS, Country and Users. Insert three line items i.e., Month 1, Month 2 and Delta

Formula in Month 1:

Formula in Month 2:

Only difference between Step 4 and Step 4(a) is one of its dimensions. Step 4 has Metrics as a dimension and Step 4(a) has LIS as a dimension