3 Replies Latest reply: Nov 7, 2012 1:40 PM by Steve Zagzebski RSS

    If statement within set analysis

    Steve Zagzebski

      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 ramasamy

          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

          • Re: If statement within set analysis
            Vlad Gutkovsky

            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