1.07-02 Line Item subsets for Version Formula

edited May 2023 in Planual

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



  • 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