4 Replies Latest reply: Oct 31, 2017 10:15 AM by Sean Drummond RSS

    Qlik Sense - Set Analysis Question

    Sean Drummond

      Hi all

       

      I need help on understanding Set Analysis in a particular scenario. I have the following table:

      Data:

      LOAD *,

      Year &'-' & Quarter as YearQtr;

      LOAD * inline [

      Row, Year, Quarter, Amount

      1, 2012, Q1, 1100

      2, 2012, Q2, 1700

      3, 2012, Q3, 1400

      4, 2012, Q4, 1800

      5, 2013, Q1, 1000

      6, 2013, Q2, 1300

      7, 2013, Q3, 1100

      8, 2013, Q4, 1400

      9, 2014, Q1, 500];

       

      I want to build a number of KPI's off this data which would determine

      a) Total amount for all quarters

      b) Total amount for YTD (in this case 2014)

      c) Total amount for Quarters minus last 4 quarters

      d) Total amount for Quarters minus last 6 quarters

       

      In my visualizations, I will have filter panel using YearQtr. I also have 4 KPI's per each calculation. Based on the selections made in the filter panel, the amounts for each should change accordingly

       

      For a) my expression wouldn't need Set Analysis, it would simply be a sum of amount, this would never change

      For the other 3 however I'm getting varying results.

      I'm basing my condition off the Row value e.g. =SUM({$<Row = {"<=$(Row) - 4"}>}Amount) but it's not working.

       

      Any help is appreciated.

       

      Regards

        • Re: Qlik Sense - Set Analysis Question
          Juraj Misina

          Hi Sean,

           

          How is your Row variable you use in set analysis defined? It should be

          =Max(Row)

          You also need to turn off YearQtr filter in your set analysis in order to enlarge the data set for the calculation based on your requirements. Then you should be able to elaborate on your expression:

          b: SUM({$<YearQtr, Row = {"<=$(Row)"}, Year={$(vMaxYear)}>}Amount)

          c: SUM({$<YearQtr, Row = {"<=$(Row) - 4"}>}Amount)

          d: SUM({$<YearQtr, Row = {"<=$(Row) - 6"}>}Amount)

           

          Variable vMaxYear is defined as =Max(Year).

           

          Best

          Juraj

          • Re: Qlik Sense - Set Analysis Question
            Sean Drummond

            Thanks Juraj, that set me on the right direction. I'm trying to expand on this a little.

            The data I have expands to over 10 years worth of values. Using the same table as before, I'm now working the set analysis so that the calculations are generated as per the selection i.e. if I select 2012-Q3, I should get all values between 2012-Q3 and 2011-Q4 for the 1 year aggregation

             

            I'm using a variable to generate the max row number (vMaxQtrSeq), so in line with the above table my expression is something like this:

            Sum({<YearQtr, Row = {">=$(=$(vMaxQtrSeq)-3), Row = {"<=$(=$(vMaxQtrSeq)) "} >}  Amount)

             

            But it doesn't work - I think that's down to the use of using the Row condition twice, I've tried to put it in the one strong to no avail:

            Row = {"'>=' & $(=$(vMaxQtrSeq)-3), & '<=' &$(=$(vMaxQtrSeq))"}


            Any ideas?