How to truncate or make this formula more efficient

neg177
neg177
edited February 2 in Modeling

Hi,

This formula in our model is possible slowing down the load time of our model. The formula has the logic we need but I believe it can be written better for loading time in our model.

Formula: 

IF 
    'S04 Version Mapping'.Budget 
THEN 
    IF 
        'S01 Time Settings'.Funding Holidays? 
    THEN 
        IF 
            'S01 Time Settings'.Weekdays? 
        THEN 
            IF 
                WEEKDAY(START()) = 1 
            THEN 
                OFFSET(SFS Funding Final, -3, 0) 
            ELSE 
                PREVIOUS(SFS Funding Final) 
        ELSE 
            0 
    ELSE 
        'Collections Funding Dayification (Funding)'.Total 
ELSE 
    IF 
        'S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day 
    THEN 
        SFS Funding[SELECT: VERSIONS.Actual] 
    ELSE 
        IF 
            'S01 Time Settings'.Funding Holidays? 
        THEN 
            IF 
                'S01 Time Settings'.Weekdays? 
            THEN 
                IF 
                    WEEKDAY(START()) = 1 
                THEN 
                    OFFSET(SFS Funding Final, -3, 0) 
                ELSE 
                    PREVIOUS(SFS Funding Final) 
            ELSE 
                0 
        ELSE 
            'Collections Funding Dayification (Funding)'.Total


Comments

  • ryan_kohn
    ryan_kohn

    Looks like your formatting got messed up a little bit. It would help if you could post a screenshot of how this formula looks in the Anaplan NMX formula editor, as well as a screenshot of your blueprint (so we can see dimensions).

    Even without the blueprint, I can see that there are some Planual best practices that are not being followed. Read through the below rules and associated best practice articles as a start:

  • neg177
    neg177

    Hi Ryan,

    Thank you for your response. Attached is a screen shot of both the Blue Print and the Formula.

    image.png
    image.png


    Thank you

  • ryan_kohn
    ryan_kohn

    Thanks for sharing the screenshot of the formula, which helps make the formula clearer. Additionally, thank you for the blueprint, which allows us to see the dimensions involved in the formula.

    Without going too far into the logic, the biggest issue I see here is your duplication of expressions (Planual rule: 2.02-03 No Repeated Expressions).

    image.png

    The entire yellow box is repeated twice, and includes repetition inside of it as well. At the very least, the yellow box needs to be its own line item.

    Additionally, I see that you've repeated WEEKDAY(START()) = 1 twice in this formula, and used the same logic in multiple other formulas in your blueprint. You need to move this repeated logic to a System module (read: Best practices for module design). For example:

    First Day of Week? = WEEKDAY(START()) = 1

    You'll also want to switch your IF statements around such that the most common condition is put first (Planual rule: 2.02-17 Put the most common condition first). Knowing the most common condition normally requires that you know your data somewhat well. Of course, you can definitely swap the First Day of Week? condition as most of the days in a week are not first. e.g.

    IF
    NOT First Day of Week?
THEN
    PREVIOUS(SFS Funding Final)
ELSE
    OFFSET(SFS Funding Final, -3, 0)

    In this overall formula, you definitely also want to think about how to reduce nested IFs (Planual rule: 2.02-01).

  • ramonito
    ramonito
    edited February 2
    1. Assign the ' = 1' to a hardcoded system module
    2. Update the select function to a lookup.
  • ryan_kohn
    ryan_kohn

    @ramonito While you are correct that normally we want to avoid SELECT, in this case, it is okay since it is on Versions.

    See exception 2.02-14a on the Planual rule for 2.02-14 Avoid using SELECT.

  • ManjunathKN
    ManjunathKN

    Hi @neg177 ,

    can you cut the entire above logic(in blue) and do OR logic with selected/circled in red.

    image.png

    Let me know if that works.


    Thanks,

    Manjunath

  • AjayM
    AjayM

    Hi @neg177 ,

    Hope you already noticed some performance improvement with above suggestions from @ryan_kohn. To avoid duplciation of same set of logic twice, a slight tweak as below should help. I still recommend to centralise repetitive expressions into a system module, and flip around IF-ELSE logics to keep most common logic fall under IF condition.

    image.png


  • ManjunathKN
    ManjunathKN

    Hi @AjayM,

    I have a question here, S04 version mapping and S01 time setting are independent, so I believe last SFS Funding[select: versions. Actual] might not process correctly. Because the argument after OR will process for every version.

    Correct me, if I am missing anything.

    Thanks,

    Manjunath

  • AjayM
    AjayM

    Hi @ManjunathKN ,

    My formula argument after OR is the opposite of primary ELSE part in original formula, which should apply for all versions after passing S04 check and actual date check. If you look at the valid cases in original formula for the repetitive logic, it should apply to S04.BUDGET version or if NOT ('S01 Time Settings'.Actual Date <= 'S02 Time Admin'.Last Actual Day).

Categories