IF condition not working

[Amy123]

IF [Cat]!= "1" OR "2" OR "3" AND [Balance*] = "R" THEN [Functional amount ] Else if [Cat] = "1" OR "2" OR "" AND [Balance*] ="R" THEN -[Trx in FC]

[atcodedog05]

Hi @Amy123 

Formula would be something like below. Provided Cat is string datatype.

IF [Cat] not in ("1","2","3") AND [Balance*] ="R" THEN [Functional amount] 
Elseif [Cat] in ("1","2","3") AND [Balance*] ="Recorded Item" THEN -[Trx in FC]
Else Null() Endif

[bensilv]

HI @Amy123 you need to list each condition, by this I mean [Cat]!= "1" OR [Cat]! = "2" OR... etc.

Alternatively, you can you the IN function, so [Cat] NOT IN (1,2,3)

Ben

[Emil_Kos · Accepted solution]

Hi @Amy123,

I  build a solution for you.

IF [Cat] IN ('1','2','3') AND [Balance*] = "R" THEN [Functional amount ] ELSEIF [Cat] IN ('1','2') OR IsEmpty([Cat]) AND [Balance*] = "R" THEN [Trx in FC] * -1 ELSE null() ENDIF

[Jonny]

Hi @Amy123 ,

try this and let me know if works?

IF ([Cat]!= 1 OR [Cat]!=2 OR [Cat]!=3) AND [Balance*] = "R" THEN [Functional amount ] Else if ([Cat] = 1 OR [Cat] = 2 OR IsNull([Cat])) AND [Balance*] ='Recorded Item' then [Trx in FC]*-1 else "" endif endif

I am assuming CAT is numeric

your if statement were missing the last Else condition, which I set to "", but you have to check

you need endif endif at the end to close the show 

cheers!

Jonny

BR/Jonny