Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey There,
I have an issue with my IF statement.
it is a dual if, where im asking it to verfy the period, and then do a calculation.
If I run each of the IF statements on their own, I get results, but when combining them, I get a - result
Please can you assist with the syntax.
if(Period < 201302,
if(ABC='ABC1' and [Flag]='X' [No of Boxes] * 15,
if(Period>= 201302,
if(ABC='ABC1' and [Flag]='X' [No of Boxes] * 20))),0)*1.14
Hello Juliette,
Can you try the below query please :
if(Period < 201302,
if((NCS='NCSNP'or NCS = 'NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX' ,[No of Rx] * 12,
if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(Period>= 201302,
if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,
if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300) and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5)))))*1.14,0)
Please let me know if this was helpful.
Thanks,
Angad
Hello,
Can you please try the below statement :
if(Period < 201302,
if(ABC='ABC1' and [Flag]='X',[No of Boxes] * 15,
if(Period>= 201302,
if(ABC='ABC1' and [Flag]='X',[No of Boxes] * 20)))*1.14 ,0)
It will return 0, if no condition is matched.
Please let me know if this was helpful.
thanks,
angad
Juliette,
maybe it's just a typo in your post, but I think you are missing commata before [No of boxes] to start your THEN branch in your if-statements:
if(Period < 201302,
if(ABC='ABC1' and [Flag]='X', [No of Boxes] * 15,
if(Period>= 201302,
if(ABC='ABC1' and [Flag]='X', [No of Boxes] * 20))),0)*1.14
Then, you are not defining an ELSE branch for every possible combination of values, so this might lead to a '-' result.
Finally, you are not using an aggregation function, so your expression will only work if you get unambiguous values for ABC, Flag, No of Boxes, Period in the context you are evaluating.
Hope this helps,
Stefan
Hi Stephan,
I was actually putting in an example of the IF statement.
Here is the actual IF statement which is not working.
I have tried everyones examples, and i cannot get it to work.
thank you
if(Period < 201302,
if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 12,
if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 12,
if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 12,
if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(Period>= 201302,
if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,
if(NCS='NCSNP' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,
if(NCS='NCS PHY 500' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,
if(NCS='NCS PHY 300' and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5)))))))))))))*1.14,0)
Are u trying this in a load statement?
If u r doing in load statement, U should get the result, and performance will increase.
Hello Juliette,
Can you try the below query please :
if(Period < 201302,
if((NCS='NCSNP'or NCS = 'NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX' ,[No of Rx] * 12,
if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5,
if(Period>= 201302,
if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300') and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'CHRONIC RX',[No of Rx] * 20,
if((NCS='NCSNP' or NCS='NCS PHY 500' or NCS='NCS PHY 300) and [Waiver Flag]='DSP' and [AcuteChronic Flag] = 'ACUTE RX',[No of Rx]*5)))))*1.14,0)
Please let me know if this was helpful.
Thanks,
Angad
It works!
Thank you Angad!!!
Woohoo
No, Im not trying in a load statement, as i am using my dimensions which i calculate.
thanks for the info though, i will try it like that later.
thanks for the help y'all
Thanks Juliette,
Can you please mark your question as answered.
Thanks,
Angad