Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Excel formula to Alteryx

mylar
6 - Meteoroid

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!

4 REPLIES 4
cmcclellan
13 - Pulsar

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
11 - Bolide

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
6 - Meteoroid

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

ancook
5 - Atom

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

Labels