17 Replies Latest reply: Jun 16, 2016 8:55 AM by Martin Mak RSS

    Expression as a variable with $-sign expansion

    Martin Mak

      I have a Pivot Table Chart with four different expressions.

      Chart working.png

      I want to change this chart in a way that it only shows one expression and I can choose the desired calculation (expression) with a button. Therefore I put the expressions in four variables.

       

      Set DefLOGC=    Count( { $ <LOGC]={‘J’}, [TACTISCH_BEGIN]=P(Date)> } Distinct ID_ODK );

      Set DefOV=         Count( { $ <LOGC]={‘N’}, [TACTISCH_BEGIN]=P(Date)> } Distinct ID_ODK );

      Set DefTOTAAL= Count( { $ <LOGC]={‘*’}, [TACTISCH_BEGIN]=P(Date)> } Distinct ID_ODK );

      Set DefVA=         Count( { $ <[VA]={‘J’}> *

                 <[PARKET_CODE]={94}, [VA_BEGIN]=P(Date)> +

                 <[PARKET_CODE]={94}, [VA_EIND]=P(Date)> +

                 <[PARKET_CODE]={40,41,42,43,44,45,46,47,48,49,93},

                              [VA_BEGIN]={‘<=$(=YearEnd(MakeDate(Max(Year))))’},

                              [VA_EIND]={‘>=$(=MakeDate(Min(Year)))’}> }

               Distinct ID_ODK );

       

      The button stores the name of the definition (DefLOGC, DefOV, DefTOTAAL or DefVA) in a variable vDef. In the chart I use the expression $(=$(vDef)). This works for the variables (expressions)  DefLOGC, DefOV and DefTOTAAL, but not for DefVA.

      Chart not working.png

      I suppose it has to do with the use of $-sign expansions in the expression, if stored as a variable. It makes no difference if I put double quotes (“”) around the expressions. I have read discussions and blogs on Qlik Community and elsewhere, but can not find a working solution for this problem. I am running out of ideas. Anyone else?

       

      I am using QlikView Version 11.20 SR10 (64-bit)