topic Example of Set Analysis with multiple If statements in QlikView

Example of Set Analysis with multiple If statements

Tue, 10 Apr 2012 17:47:00 GMT

Does anyone have any examples of Set Analysis with multiple If statements? I need to have certain accounts set to one year where other accounts set to another year.

Example of Set Analysis with multiple If statements

nilesh_gangurde — Tue, 10 Apr 2012 18:30:29 GMT

Heyyy can you explain it in detail...

Example of Set Analysis with multiple If statements

Tue, 10 Apr 2012 18:45:29 GMT

So I would like my data to return independently on selections on listboxes, but hardcoded into a straight table. So I have list of Accounts like below in a column in a table. There's a Values column next to it as seen below:

EmplyAcnt Values(Signdata)

Salary 120,000

Bonus 23,000

Recommended Merit 2,000

Target Bonus 25,000

Recommended Bonus 24,000

I would like the chart to pull accounts: Salary, Target Bonus, Bonus related to year 2011.

I would like the chart to pull accounts: Recommended Merit, Recommeneded Bonus related for year 2012

I found a way to do this, but didn't know if there were better ways to do it.

=SUm({<EMPLYACNT={SALARY,T_BONUS,BONUS}>}if(TIMEID='20110000',SIGNEDDATA)) + SUM({<EMPLYACNT={R_MERIT,R_BONUS}>}IF(TIMEID='20120000',SIGNEDDATA))

Re: Example of Set Analysis with multiple If statements

Tue, 10 Apr 2012 18:49:02 GMT

EmplyAcnt Values(Signdata)

Salary 120,000

Bonus 23,000

Recommended Merit 2,000

Target Bonus 25,000

Recommended Bonus 24,000

I would like the chart to pull accounts: Salary, Target Bonus, Bonus related to year 2011.

I would like the chart to pull accounts: Recommended Merit, Recommeneded Bonus related for year 2012

I found a way to do this, but didn't know if there were better ways to do it.

=SUM({<EMPLYACNT={SALARY,T_BONUS,BONUS}>} IF (TIMEID='20110000',SIGNEDDATA)) + SUM({<EMPLYACNT={R_MERIT,R_BONUS}>}IF(TIMEID='20120000',SIGNEDDATA))

Example of Set Analysis with multiple If statements

jagan — Wed, 11 Apr 2012 05:57:42 GMT

Hi,

The below expression is the optimized one without using IF statement. Using IF degrades the performance of the expression.

=SUM({<EMPLYACNT={SALARY,T_BONUS,BONUS}, TIMEID={'20110000'}>} SIGNEDDATA) + SUM({<EMPLYACNT={R_MERIT,R_BONUS}, TIMEID={'20120000'}>} SIGNEDDATA)

Hope this helps you.

Regards,

Jagan.

Re: Example of Set Analysis with multiple If statements

Wed, 11 Apr 2012 06:34:25 GMT

Actually in my statement i used only 1 If statement with set analysis and it is working but i didnt use nested if statements, i think it will work.you can try out with that same example.

yojas

Example of Set Analysis with multiple If statements

Wed, 11 Apr 2012 13:55:53 GMT

Yojas - It didn't work with adding two IF nested statements in the example you had, so I had to use my double sums in my code. However with Jagan's responds, I didn't have to use the IFs, so it works out.

Jagan - your optimized code worked and thus doesn't require me to use the IF statements. Thanks!

Re: Example of Set Analysis with multiple If statements

Fri, 20 Apr 2012 04:10:33 GMT

actualy i tried it for some different example but multiple if statements are not working.....but still i am having confusion about if 1 if statement is working with set analysis then why multiple if statements are not working???

yojas

Re: Example of Set Analysis with multiple If statements

Wed, 18 Jul 2012 10:48:31 GMT

Hi Jagan,

I am also doing a certain calculation which is very similar to the above example. However, in my case its not adding up, instead its giving me the total for the first statement. For eg - i want the volume of 2011 for the month of april and may to be added to this year's 2012. I am using the below statement but its not giving me the total, instead its giving me the total of 2011 only -

=sum({<MONTH={04,05},YEAR={2011}>}[VOLUME (M QTY)])+sum({<MONTH={05},YEAR={2012}>}[VOLUME (M QTY)])

Please help

Thanks

Anirban

Re: Example of Set Analysis with multiple If statements

Wed, 18 Jul 2012 10:56:06 GMT

Thanks! is was helpfull solving my problem at http://community.qlik.com/message/239050#239050