Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

If statement within set analysis

I am struggling with an if statement I have in an expression of my chart. I am probably way off but the main thing I am trying to accomplish is that if the selcted current month is 'Jan", then sum the data based on a specfic set analysis, if not then sum based on a different set analysis.

if(

     ='Jan',

     sum({<[Incurred Year]={$(vMaxYear-1)},[Count Type]={'ACTUAL'},[Incurred Month Num]=12>}[Member Count Adjusted]),

     sum({<[Incurred Year]={$(vMaxYear)},[Incurred Month]=,[Count Type]={'ACTUAL'},[Incurred Month Num]= {$(=vMaxMonth-1)}>}[Member Count Adjusted])

     )

3 Replies
MayilVahanan

Hi

Try like this

=if(GetFieldSelections(Month) = 'Jan',1,0)

edit:

  if(GetFieldSelections(Month) = 'Jan',

     sum({<[Incurred Year]={$(vMaxYear-1)},[Count Type]={'ACTUAL'},[Incurred Month Num]=12>}[Member Count Adjusted]),

     sum({<[Incurred Year]={$(vMaxYear)},[Incurred Month]=,[Count Type]={'ACTUAL'},[Incurred Month Num]= {$(=vMaxMonth-1)}>}[Member Count Adjusted]))

  Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
vgutkovsky
Master II
Master II

The way QlikView works, you will get better performance if you take your IF statement into the variable. The variables vMaxYear and vMaxMonth start with an '=' (or at least it should). That means that the variables will be calculated irrespective of chart dimensions, so the whole thing will load quicker. If you include the IF statement in the chart expression, it will be evaluated for each dimension, which will cause a slowdown. Now, you may not actually care about performance if your data set is small, but if it's at all sizeable, I would suggest doing the following with 2 new variables:

vLastMonthYear

= if(getfieldselections(Month)='Jan',vMaxYear-1,vMaxYear)

vLastMonth

= if(getfieldselections(Month)='Jan',12,vMaxMonth-1)

Chart expression

sum({<[Incurred Year]={$(vLastMonthYear)},[Count Type]={'ACTUAL'},[Incurred Month Num]={$(vLastMonth)},[Incurred Month]=>}[Member Count Adjusted])

Regards,

Vlad

zagzebski
Creator
Creator
Author

Excellent. this worked great. Thanks much.