Trouble with IF logic on formula step

COREY
COREY Registered Posts: 18 ✭✭✭✭

Getting some unexpected results with this formula step where some retired customers are showing the max of L12/2019/2020/2021/2022 when they should be showing L12. What I'm looking to do is this:

- If a customer is retired (using Retired Flag = TRUE) or if they have bad debt (using Bad Debt Status =TRUE), I want to always return the Total L12 value. If neither of those things are true, I want to return the highest value between the L12 value, the 2019, 2020, 2021, and 2022 values.

The bad_debt_status field and the retire flag field are both boolean.

if(val("Retire Flag") == "true",val("Total (L12) Value"),
if(bad_debt_status,val("Total Wallet (L12) Value"),
max(2019_Spend, 2020_Spend, 2021_Spend, 2022_Spend,
val("Total (L12) Value"))))


Operating system used: Windows


Operating system used: Windows

Best Answer

  • COREY
    COREY Registered Posts: 18 ✭✭✭✭
    Answer ✓

    I figured it out. Just needed to add an 'OR' to this and it works as expected.

    if(or(val("Retire Flag")== "TRUE",val("bad_debt_status")) == "TRUE",
    val("Total (L12) Value"),
    max(2019_Spend, 2020_Spend, 2021_Spend, 2022_Spend,
    val("Total (L12) Value")))

Setup Info
    Tags
      Help me…