Example of Set Analysis with multiple If statements

[#1]

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. 

[nilesh_gangurde]

Heyyy can you explain it in detail...

[#3]

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))

[#4]

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))

[jagan · Accepted solution]

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.

[#6]

Hi

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

[#7]

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!

[#8]

hi

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

[#9]

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

[#10]

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