Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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(
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])
)
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
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
Excellent. this worked great. Thanks much.