Conditional logic: Does a list item appear in a subset?

tristan.colgate
tristan.colgate
in Best Practices

Subsets are invaluable in creating dynamic and efficient models in Anaplan. They are typically used as a dimension in a module, where only the subset is relevant.

Take, for example, a list named Employee containing a list of all employees in your organization. Employees are either Sales Executives or Engineers:

1.png

A module for holding sales quotas by employee would use the Sales Executive subset as the module dimension. This is logically correct, ensuring that sales quotas are not inadvertently stored for Engineers. Moreover, this helps to manage the model size by ensuring redundant data (i.e. sales quotas for Engineers) is not stored.

2.png

The challenge comes when you want to use conditional logic in a line item and use a formula that produces a different result depending on whether a list item appears within a subset or not.

In our example, let’s say we’re modeling headcount expenses by employee and all Sales Executives receive a car allowance of $10,000 per annum. We need a way of determining whether an employee is in the Sales Executive subset or not.

There is no standard Anaplan formula to achieve that, but the screenshot below shows how to achieve this. Please note the following:

  1. We calculate two Boolean line items – is Sales Executive? And is Engineer?
  2. These are line items in the Employee Details SYS module. Note—this is best practice, in contrast to storing the results as properties of the list.
  3. While the module is dimensioned on Employee, the Applies To field of each line item is set to the corresponding subset
  4. Each line item is hard-coded to TRUE, which, mixed with the Applies To restriction on the subset, ensures that only items in that subset are set to TRUE

3.png

You can see from the screenshot below that the Booleans calculate correctly, showing which subset for which each employee is a member.

4.png

You can then go on to refer to the two Boolean line items in this Employee module in the normal way. In the following screenshot, we are referring to the is Sales Executive? Boolean in a calculation module where we are calculating car allowance. Only sales executives should receive a car allowance of $10,000 per month:

5.png

We can now see that only our sales execs are receiving a car allowance:

6.png

 

 

Tagged:

Comments

  • JaredDolich
    JaredDolich

    @tristan.colgate 

    That is so cool. What a clean way to obtain the members of a list subset.

    And it follows the DISCO method too! Brilliant!

    Can this method be used to balance a ragged list somehow? Sometimes all that's needed is the child level and the parent within all those levels.

    Anyway, this is going in my personal Planual.

  • tristan.colgate
    tristan.colgate

    Thanks @JaredDolich , glad you like it!

    Interesting point about ragged hierarchies - I'll put a bit of thought to that.

  • ChrisWeiss
    ChrisWeiss

    @tristan.colgate Really great article! Here I was thinking I had the right solution by using a boolean in a CALC module with the formula ISNOTBLANK(FINDITEM(Sales Rep, CODE(ITEM(Employee)))).

    Your solution here is a much more elegant formula, AND limits sparsity by only applying the calculation where the result would be true (though sparsity issues are already minimal for booleans).

    Love this idea to optimize this calculation event further, thanks for sharing!

Categories