topic If statement within set analysis in QlikView

If statement within set analysis

zagzebski — Tue, 06 Nov 2012 21:37:39 GMT

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

)

Re: If statement within set analysis

MayilVahanan — Tue, 06 Nov 2012 21:41:31 GMT

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

Re: If statement within set analysis

vgutkovsky — Tue, 06 Nov 2012 22:08:02 GMT

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

Re: If statement within set analysis

zagzebski — Wed, 07 Nov 2012 18:40:50 GMT

Excellent. this worked great. Thanks much.