IF Formula - For combining a combination of criteria

svbhagat
svbhagat
in Modeling

Hello,

I'm hoping I can get some help with using an IF formula to combine a criteria.  

The following three line item formulas are working correctly:

clipboard_image_4.png

Then I have the following two line items that show what list items should be ignored.

clipboard_image_5.png

Where I am having trouble is where I try to combine the two criteria in blue above and then still pull the data.

clipboard_image_6.png

Here is the data:

clipboard_image_7.png

If the data is a combination of Year 2018 and Company AU 100-517, then it should be ignored otherwise everything else should be included.

Column F and G - Should be pulling data only for 2018-100-500; 2019-100-500; 2019-100-517.  But currently it is only pulling data for 2019-100-517.  How can I update the formula to pick up the other two criterias?

 

Thanks for the community's help in advance.  

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @svbhagat, what a great question.

     

    I would suggest all your conditionals be created in system modules. You can learn more by reading about the DISCO methodology.

    System modules are single dimensional modules, even time, where you put all your Booleans for use in conditional statements.

    There are several advantages to this:

    1. you only calculate the Boolean once.

    2. you can combine them to create more complex Booleans, like the ones you're trying to create above.

    3. the conditionals are more readable, and thus auditable (PLAN methodology)

    4. your formulas are more efficient because  you only calculated the Boolean once

    5. Booleans are resuable, meaning you can also use them for filtering <- nice Anaplan feature.

     

    Lastly, by putting the most likely condition first in your IF statement, you will prevent Anaplan from calculating the remainder of the formula making your calculations even faster.

     

    Here's a small example to show you what I mean.

    First set up your system modules and add your Booleans.

    You'll need one for company and time. In this case, I'll show you time.

    Notice the Boolean is calculated once. From now one we'll refer to this instead of calculating it again.

    Conditionals-01.png

    Then in your output or calculation module, you will refer to this Boolean in your conditional.

    Remember to always pick the most likely condition first so Anaplan will exit the calculation sooner.

    In this case the invalid years are more likely (there are 6 of them and only 5 valid years)

     

    Conditionals-02.png

    And the result is what we wanted - only valid years are caculated.

    Now, just repeat this process for companies.

    Conditionals-03.png

  • rob_marshall
    rob_marshall

    @svbhagat ,

     

    I would continue what @JaredDolich wrote, but add the dimension Company AU - Concat PBI with time at the year level. That way, you can now be fully scalable and be able to ignore multiple years and multiple Company AU-Concat PBI's by the checking of a Boolean.

     

    Hope this helps,

     

    Rob

  • svbhagat
    svbhagat

    Hello Jared,

    Thank you for the screenshots and methodologies provided.  I will look into it and reach out if I have any additional questions.  

  • svbhagat
    svbhagat

    Hello Rob,

    Thank you for the suggestion about adding that dimension in and setting up a boolean.  I will work on these suggestions and solutions and see what I am able to create.  I will get back to you if I have any additional questions.

     

  • JaredDolich
    JaredDolich

    @rob_marshall,

    Quick question. A learning point for me, I guess.

    Would you recommend that companies be added to the same time system module or that a separate company module be built since it looks like there are going to be multiple Booleans? That way @svbhagat can reuse the time and company system modules. For example, a module that has time and products and @svbhagat only wants valid years.

  • rob_marshall
    rob_marshall

    @JaredDolich ,

     

    If I am understanding your question correctly, it should be in the same module as it should be the same dimensionality (time at the year level by Company).  Even though there could be multiple Booleans or line items, the dimensionality will be the same so there is no need for additional modules.  Additionally, when another model builder "needs" this dimensionality, they should create the logic in this module instead of subsidiary view in their module.  Think of it as a "store" where you will go to find goods they sell.  You wouldn't go to a tire store for bread, which would be considered a subsidiary view.

     

    Does this help?

     

    Rob

  • JaredDolich
    JaredDolich

    Thanks @rob_marshall. Makes sense.

Categories