Multiple IF Statement with Multiple AND Statement

[quims]

HI Alteryx Community,

I'm having difficulty when combining IF statement with multiple AND. 

Here is my current statements with the incorrect results:

IF [Brand] = "EDU" AND
[Brand] = "LOG" AND
[Brand] = "PSW" AND
[Brand] = "PSMW" AND
[Brand] = "PSMC" AND

[COS] =0
THEN [Sales] + [COS]
ELSE ([Total Sales]*[COS])/[Total COS] ENDIF

The default is correctly calculated.

SO No.	SO Line#	Item	Brand	Class	Revenue	Expense	Total Revenue	Total Expense	Allocation	Should be	Remarks
1111112	20	AAA	PSW	VSMW	-791	678	-1582	1356	-791	-113	Correct
1111114	10	CCC	APP	HW	-121.2	113.71	-121.2	113.71	-121.2	-121.2	Correct
1111115	10	DDD	EDU	SVC	-4528.67	3816.44	-4528.67	3816.44	-4528.67	-712.23	Error
1111116	90	EEE	EDU	SVC	-320	0	-15645	15645	0	-320	Error
1111117	10	FFF	EDU	SVC	0	-1562.4	0	-1562.4	0	-1562.4	Error
1111118	10	GGG	PSMW	SVC	-135256.33	120000	-135256.33	120000	-135256.33	-15256.33	Error
1111119	10	HHH	PSMW	SVC	-441.54	0	-441.54	0	0	-441.54	Error
1111120	10	III	PSMW	VSMW	-29476	2970	-29476	2970	-29476	-26506	Error
1111121	20	JJJ	PSMW	VSMW	-8093.82	0	-64714.22	0	0	-8093.82	Error
1111122	90	KKK	PSMC	VSMW	-2586.29	0	-100303.54	0	0	-2586.29	Error
1111123	100	LLL	PSMC	VSMW	-29476	2970	-29476	2970	-29476	-26506	Error

[danespoors]

Hi @quims ,

As you have a column containing multiple values that you're looking to test, I would suggest that you use the 'IN' function like so:

IF [BRAND] in ("EDU", "LOG", "PSW", "PSMW", "PSMC") AND [cos] = 0 THEN ... 

This way it is one column with multiple checks for values.

Hope this helps,

Dane.

[quims]

@danespoors 

Sorry but the [cos] = 0 is only taking effect for the formula [Revenue] + [Expense]

quims

[RolandSchubert · Accepted solution]

Hi @quims ,

I think the problem is connecting the conditions using "AND" - currently the IF-clause is only true, if [Brand] is e.g. both "EDU" and "LOG" - and one of the conditions will not be true (it's "EDU" or "LOG" or ...). 

IF [Brand] = "EDU" OR [Brand] = "LOG" OR [Brand] = "PSW" OR [Brand] = "PSMW" OR [Brand] = "PSMC" OR [COS] = 0 THEN
[Sales] + [COS]
ELSE

([Total Sales]*[COS])/[Total COS]

ENDIF

A shorter version would be:

IF [Brand] IN ("EDU", "LOG", "PSW", "PSMW", "PSMC")  OR [COS] = 0 THEN
[Sales] + [COS]
ELSE

([Total Sales]*[COS])/[Total COS]

ENDIF

Hope this is helpful.

Best,

Roland

[quims]

Wow! Thank you @RolandSchubert,

It is now working perfectly!

Thank you for the help!

Thanks too, @danespoors.

quims