Conditional IF statement in Alteryx

[AhanaR]

Hi Team,

Could someone help in replicating this formula to Alteryx? I'm adding two criteria to calculate the Price and this is how I would write the formula in excel (replaced cell numbers with col names) I tried to write the same logic in alteryx, but it was giving me an error at greater than symbol.

IF(OR(ASP(Local)="",Unit Price In Backlog =""),0,IF(OR(Price Times<0.5,Price Times>5,Volume Exclusion="Yes"),0,(Unit Price In backlog-ASP(Local))*Backlog Quantity in Base Unit))

[AhanaR]

Dataset Used :

Unit Price In Backlog	Price Times	volume exclusion	Backlog Value in Document Currency	ASP (Local)	Backlog Quantity in Base Unit
7247.69	1.18	NO	7247.69	150	1
3692.31		NO	3692.31	240	1
17.03	0.86	NO	136.24	163	8
54.82	1.04	NO	54.82	90	1
9.35	1.18	NO	215.05	405	23

[ShankerV]

Hi @AhanaR 

Please use the below.

IF [ASP(Local)]="" OR [Unit Price In Backlog] =""
THEN 0
ELSEIF [Price Times]<0.5 OR [Price Times]>5 OR [Volume Exclusion]="Yes")
THEN 0
ELSE ([Unit Price In backlog]-[ASP(Local)])*[Backlog Quantity in Base Unit]
ENDIF

[ShankerV · Accepted solution]

Hi @AhanaR 

One way of doing this.

IF Isnull([ASP (Local) ]) OR Isnull([Unit Price In Backlog])
THEN 0
ELSEIF [Price Times]<0.5 OR [Price Times]>5 OR [volume exclusion]="Yes"
THEN 0
ELSE ([Unit Price In backlog]-[ASP (Local) ])*[Backlog Quantity in Base Unit]
ENDIF

Note: Data cleansing and select tool are to normalize the data in the dataset.

Many thanks

Shanker V