8 Replies Latest reply: May 17, 2016 2:51 AM by David Maurice RSS

    Set Analysis - calcuating changes over time

    David Maurice

      I have a request to show certain KPIs with monthly changes - imagine the KPI tool in Sense with the current value as the major component and the change as the minor component.

      I wanted to try and keep this all in Qlik Sense, so what better way than to do it with Set Analysis! However my Set Analysis expression works, but not in the way I thought it would.


      Taking some test data, I set up a "calendar" table that holds the current period, as well as the previous period so I can reference it in my set expressions:

      Calendar:
      load * inline
      [
      yrMonth, previousYrMonth
      201601, 201512
      201512, 201511
      201511, 201510
      ];

      monthdata:
      load * inline
      [
      yrMonth, CategoryID, sales
      201601, 1, 1600
      201512, 1, 1500
      201512, 2, 1500
      201511, 1, 50
      201511, 3, 100
      ];

       

      So with this data, I have the following set expression:

      Sum({$<yrMonth=P({1<yrMonth={$(=previousYrMonth)}>}yrMonth)>}sales)

       

      Which I take as, where yrMonth equals the value in previous YrMonth, for those matching values in yrMonth based on the current selection of yrMonth take the sum of sales? This works only when I have a month selected as a filter, which is clearly not ideal!

       

      Because alone, if I use that expression in something like a KPI with a month selected as a filter, it works fine ,(201601 selected)

      (201601 selected)

       

      sum(Sales)Sum({$<yrMonth=P({1<yrMonth={$(=previousYrMonth)}>}yrMonth)>}sales)
      16003000

       

      but if I include yrMonth itself as a dimension, I get the following:

      yrMonthsum(Sales)Sum({$<yrMonth=P({1<yrMonth={$(=previousYrMonth)}>}yrMonth)>}sales)
      20160116000
      20151203000
      20151100

       

       

      Clearly the expression is calculating the sum correctly, but on the previous month in the table (0 otherwise), hence summation on a single selected value works, but not ideal....

       

      So my question is, can I get a formula like this working in this fashion, or should I just create it in the source data instead?