Trouble with IF logic on formula step
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
-
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")))