I need help with my equation

AndreaRGX

I have a multi-tiered equation based on criteria. Initially, I have many nested Case statements, but it didn't work so I tried to simplify. I need an equation based on the job open date, the customer, the proceeds A and proceeds B. So before 10/1 we had a straight fee based on a % of the proceeds A. After 10/1, we use a % of the Proceeds-Charge. If Proceeds A AND Proceeds B are >0, then the charge is spread across Proceeds A and B. However, there is a minimum fee of $250 after 10/1 for customer A but no minimum fee for customer B. Here is my sample "data". I have not been able to get the Minimum to be implemented in the data. I can easily build the equation in Excel, but can't seem to translate it to Domo ETL.

Job Open Date	Customer	Proceeds A	Proceeds B	Charge	Original Fee	New Fee A	New Fee B
1/1/2024	A	10	0	20	4	4	0
10/25/2024	A	20	0	20	8	250	0
10/18/2024	BIG	-25	0	20	0	0	0
10/19/2024	BIG	0	60	20	0	0	12
10/1/2024	A	1000	100	20	400	294.55	29.45
8/15/2025	A	15	0	20	6	6	0

CASE
when date(Job Open Date)<'10/01/2024' then Original Fee
WHEN Proceeds A < 0 AND INSTR(Customer,'B')>0 then 0
WHEN Proceeds A< 0 then 250
WHEN Proceeds A=0 AND Proceeds B>0 then 0
WHEN Proceeds B=0 AND INSTR(Customer,'B') > 0 AND (Charge+Proceeds A)<=0 then 0
WHEN Proceeds B=0 AND INSTR(Customer,'B') > 0 then (Charge+Proceeds A)*.3
WHEN Proceeds B=0 AND (Charge+Proceeds A)<=0 then 250
WHEN Proceeds B=0 then (Charge+Proceeds A)*.3
When (Charge(Proceeds A/(Proceeds A+Proceeds B))+Proceeds A)*.3 <=250 then 250
ELSE round((Charge(Proceeds A/(Proceeds A+Proceeds B))+Proceeds A)*.3 ,2)
END

rco

In addition to the correction mentioned by @ggenovese, this segment also needs to change: date(Job Open Date)<'10/01/2024' . An unfortunate quirk of Magic ETL is that comparison operations with strings always happen as strings, which is usually not what you want. You must convert the right side of the comparison to a date as well: date(Job Open Date)<date('10/01/2024')

ggenovese

I'm having a little trouble following the logic, it might be helpful if you shared your excel formula. Is the case statement you shared supposed to produce the New Fee A value, and you'll have another for New Fee B?

Also, should this part of the expression:

 (Charge(Proceeds A/(Proceeds A+Proceeds B))+Proceeds A)

be changed to:

 (Charge * (Proceeds A/(Proceeds A + Proceeds B))+Proceeds A)

rco

In addition to the correction mentioned by @ggenovese, this segment also needs to change: date(Job Open Date)<'10/01/2024' . An unfortunate quirk of Magic ETL is that comparison operations with strings always happen as strings, which is usually not what you want. You must convert the right side of the comparison to a date as well: date(Job Open Date)<date('10/01/2024')