How to truncate or make this formula more efficient

neg177 · 2023-02-01T20:23:34+00:00

There was an error rendering this rich post.

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

Accepted answers

All comments

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

Hi Ryan,

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

Thank you

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).

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

Assign the ' = 1' to a hardcoded system module
Update the select function to a lookup.

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

Hi @neg177 ,

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

Let me know if that works.

Thanks,

Manjunath

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.

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

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).

Quick Links

Can you help?

Unanswered questions

Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…
Curious Comparison: Forecast Modeling for Unusual Products
I've been experimenting with some forecast modeling techniques in Anaplan recently and decided to test them on a few quirky, offbeat product categories just for fun. One of the categories I played around with was love dolls—yes, you read that right. It actually turned out to be more interesting than expected. Unlike…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled