Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Nested IF formula optimization

Maybe it's obvious but somehow I got stuck with this formula. I'm convinced there is a better way to formulate this, but just can't figure it out. To make it worse, it doesn't work correctly :smileyf...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    One thing you could do would be to define this variable:

     

    VAR LastModifiedDuration = backlogRecord[Date modified], NOW (), HOUR )

     

    Also, I would switch to a SWITCH statement.

  • Anonymous's avatar
    Anonymous
    8 years ago

    Greg_Deckleryes it did the trick! Thank you. Here's my new formula for that calculated column:

     

    backlogHealth =
    VAR hoursElapsed =
        DATEDIFF ( backlogRecord[Date modified], NOW (), HOUR )
    RETURN
        SWITCH (
            TRUE (),
            hoursElapsed > 168, "Retired",
            WEEKDAY ( NOW () ) = 7
                && hoursElapsed > 48, "Missing",
            WEEKDAY ( NOW () ) = 1
                && hoursElapsed > 72, "Missing",
            WEEKDAY ( NOW () ) < 7
                && hoursElapsed > 24, "Missing",
            HOUR ( backlogRecord[Date modified] ) < 10, "OK",
            HOUR ( backlogRecord[Date modified] ) < 12, "Pending",
            "Delayed"
        )

WCrayger's avatar
WCrayger
Frequent Visitor
6 years ago

Hi Greg,

 

Wondering if you can offer input to a similar scenario. I have a measure using nested if's that referene 3 other measures and 3 different fields, so a SWITCH won't work (to my knowledge). Independantly, the measures perform fine, but in the nested if they choke.

Any feedback or suggestions would be great.

Thank you.

 

IF(
    [Work Order Invoice Total] > 0
    ,[Work Order Invoice Total]
    ,IF(
        [Work Order ISP Charge] > 0
        ,[Work Order ISP Charge] * 1.3
        ,IF(
            [Work Order Client NTE Total] > 0
            ,[Work Order Client NTE Total]
            ,350
        )
    )
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

I would use the nested if to calculate a column:

 

IF(
    [Work Order Invoice Total] > 0
    ,"A"
    ,IF(
        [Work Order ISP Charge] > 0
        ,"B"
        ,IF(
            [Work Order Client NTE Total] > 0
            ,"C"
            ,"D"
        )
    )
)

 Now you can use SWITCH based on A, B, C, D values in the calculated column.