Convert Excel formula to Alteryx

[mylar]

Hi there,

Im trying to use the same formula I used on excel, on an alteryx workflow, but the language is somewhat different.

How can I translate this formula, to alteryx language?

=IF(AR137>0,IF(J137-K137<7,"Overdue",IF(J137-K137<10,"To Expire",IF(H137>J137,"Irrational",""))),"")

AR Column = Open qty

J Column = Cancel Date

K Column = Today Date

H Column = Confirm Date

Thanks in advance!

[cmcclellan · Accepted solution]

Try .. 

IIF([Open qty]>0,IIF(DateTimeDiff([Cancel Date],[Today Date],"day")<7,"Overdue",IIF(DateTimeDiff([Cancel Date],[Today Date],"day")<10,"To Expire",IIF(DateTimeDiff([Confirm date],[Cancel Date],"day")>0,"Irrational",""))),"")

I'm guessing a few things here, so it might not be right the first go.  Can you supply some sample data if it doesn't work ?

[ncrlelia]

Hi @mylar,

I believe cmcclellan's suggestion will work. Just sharing a different (my preferred) way of writing: 

IF [Open qty] > 0
THEN IF DateTimeDiff([Cancel Date],[Today Date], 'day') < 7
           THEN 'Overdue'
           ELSEIF DateTimeDiff([Cancel Date],[Today Date], 'day') < 10
           THEN 'To Expire'
           ELSEIF [Confirm Date] > [Cancel Date]
           THEN 'Irrational'
           ELSE ''
           ENDIF
ELSE ''
ENDIF

Hope this helps.

Cheers, 

Lelia

[mylar]

Thank you both! It is very helpful and works perfectly 🙂

[ancook]

Hi there same question, 

Im trying to use the same formula I used on excel, on an alteryx workflow, but the language is somewhat different.

How can I translate this formula, to alteryx language?

=IF(I2=1,"G",IF(AND(H2>I2,H2>K2,H2>L2),"Y",IF(OR(K2=1,L2=1,L2>H2,K2>H2,AND(H2>I2,K2>H2),AND(H2>I2,L2>H2)),"R","check")))

H Column = % Scan Thru

I Column = In Stock Rate

K Column = OOS Rate

L Column = Unavailable Rate