3 Replies Latest reply: Mar 28, 2017 6:57 AM by Graham Crooks RSS

    Seeking correct syntax when using variable as constraint in set analysis

    Graham Crooks

      Dear Community

      I'm using Qlik Sense 3.1 SR4 here; I'm aiming for something of a YTD calculation, based on the Year up to the point the data is loaded and it's being plotted in a stacked bar chart in comparison with previous years.

       

      I have EventDate values in the data and from this I'm deriving EDateNumMonth from Num(Month(EventDate)), giving a number value for Month in each data row. Meanwhile, a control variable, vEDateNumMonthToday is set in the load script; Let's say we're in March, so 3 then.

       

      Now in Qlikview (v11) I used an expression logic that ran like this:

           sum({<FieldNumMonth={'<=$(vTheCurrentMonth)'}>}RCounter).

      This worked great and provided a YTD total for each year present in the data.

       

      I'm trying to employ the same approach in our QlikSense rendition, but it seems something is skewed.

      If I hard code the limitation, as:

           sum({<EDateNumMonth={'<=3'}>}RC)

      or  sum({<EDateNumMonth={'>=1<=3'}>}RC)

      everything is fine - all adds up as it should.

       

      As soon as I add a dollar expansion to the set analysis:

        

           sum({<EDateNumMonth={'<=$(vEDateNumMonthToday)'}>}RC) - the columns disappear, or

           sum({<EDateNumMonth={'>=1<=$(vEDateNumMonthToday)'}>}RC) - the columns display the total for the whole year!

      Uuh?

      Am I missing something here?

       

      @MichaelTarallo, or other community colleagues; Any constructive suggestions would be most welcome.

      Regards

      Graham