Trouble with IF logic on formula step

Solved!
ccecil
Level 3
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

0 Kudos
1 Solution
ccecil
Level 3
Author

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

View solution in original post

0 Kudos
1 Reply
ccecil
Level 3
Author

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

0 Kudos

Labels

?
Labels (2)
A banner prompting to get Dataiku