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]
Solved! Go to Solution.
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
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
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
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