6 Replies Latest reply: Sep 18, 2018 10:40 AM by Diego Alejandro Velez Becerra RSS

    Set Analysis with simple operator

    Diego Alejandro Velez Becerra

      Hello smart people from this community,

       

      I come here searching for help and doing a simple question that I haven't been able to solve by research,

       

      I have been trying to create an expression in which I want to sum the values from the PERIOD displaying and some other PERIODS above but without using the "above" formula, this is because I don't want the chart values to be affected by PERIOD Filters.My data is something like this:

       

      PERIODVALUE
      201709100
      201710200
      201711125
      201712300
      201801326
      201802225
      201803185

       

      So in my expression I want

      for 201803 to sum The values from 201803,(201803)-1,(201803)-2

      for 201802 to sum The values from 201802,(201802)-1,(201802)-2

      etc...

       

      So I have something like this:

       

      Sum(VALUE])

      +

      Sum({<PERIOD={$(=(PERIODO-1))}>} [VALUE])+

      Sum({<PERIOD={$(=(PERIODO-2))}>} [VALUE])

       

      I know there is something wrong with the sintaxis but I don't really know how is the correct way.

       

      Thanks a lot for helping me with this.

       

      (PS. I know the formula isn't gonna work when it gets between 2017 and 2018 but I have this already covered with an "if" and a "mid" formula)

       

      Honestly,

      Diego Vélez,

      Qlik Sense Student.

        • Re: Set Analysis with simple operator
          Vu Nguyen

          Because the sum value is fixed for each period, you should implement this logic in the back end (reload script) rather than a chart expression. In reload script you can use Peek() function to access 1 and 2 periods before the current period. Sample script is as follows:

           

          Data:
          LOAD * INLINE [
              PERIOD, VALUE
              201709, 100
              201710, 200
              201711, 125
              201712, 300
              201801, 326
              201802, 225
              201803, 185
          ];
          
          Temp_Data:
          LOAD 
          PERIOD,
          Sum(VALUE) as SUMVALUE
          Resident Data
          Group By PERIOD
          Order By PERIOD;
          
          Left Join (Data) 
          LOAD 
          PERIOD,
          If(RowNo() > 2, SUMVALUE + Peek('SUMVALUE',RowNo()-2,'Temp_Data') + Peek('SUMVALUE',RowNo()-3,'Temp_Data')) as SUMVALUE
          Resident Temp_Data;
          
          DROP TABLE Temp_Data;
          

           

          After reload your data table should look like this. You're free to select on field PERIOD without affecting SUMVALUE.

          Data.png

          • Re: Set Analysis with simple operator
            Stefan Wühl

            IMO, one of the best solutions for requirements like this is using an AsOf Table for your time dimension, a link table that links a period to a set of other periods:

            The As-Of Table

             

            Other options, including a version with above() that also works with selections in your dimension field, are discussed in

            Calculating rolling n-period totals, averages or other aggregations

              • Re: Set Analysis with simple operator
                Diego Alejandro Velez Becerra

                Hi Stefan, thanks a lot for your answer, I'm trying to use The As-Of Table method, for this I read that I need to create a Master Calendar first, so i did a little research about it but it isn't working like expected, could you (or anyone reading this) help me to find what am I doing wrong, I used the next script:

                 

                (in Ventas table I created a new File with the next script)

                (date#('01/'&mid(PERIOD,5,2)&'/'&mid(PERIOD,1,4),'dd/mm/yyyy')) as Date

                So I could get my PERIOD in Date format.

                 

                Then i proceeded to make my master calendar:

                "Master Calendar":

                LOAD

                FECHA AS Date,

                  Year(FECHA) AS Year,

                  Date(MonthStart(FECHA),'YYYY MMM') as Month,

                  Weekday(FECHA) AS WeekDay,

                  inyeartodate(FECHA, maxdate, 0) * -1 AS CurYTDFlag,

                  inyeartodate(FECHA, maxdate, -1) * -1 AS LastYTDFlag

                ;

                //=== Generate a temp table of dates ===


                LOAD

                  date(mindate + IterNo()) AS FECHA,

                maxdate // Used in InYearToDate() above, but not kept

                WHILE mindate + IterNo() <= maxdate

                ;

                //=== Get min/max dates from ventas table ===/


                LOAD

                  min(FECHA)-1 as mindate,

                  max(FECHA) as maxdate

                RESIDENT Ventas

                ;


                // ======== Create a list of distinct Months ========

                tmpAsOfCalendar:

                Load distinct Month

                  Resident [Master Calendar] ;


                // ======== Cartesian product with itself ========

                Join (tmpAsOfCalendar)

                Load Month as AsOfMonth

                  Resident tmpAsOfCalendar ;


                // ======== Reload, filter and calculate additional fields ========

                [As-Of Calendar]:

                Load Month,

                  AsOfMonth,

                  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,

                  Year(AsOfMonth)-Year(Month) as YearDiff

                  Resident tmpAsOfCalendar

                      Where AsOfMonth >= Month;


                Drop Table tmpAsOfCalendar;


                What I'm getting is the next data:


                asdasd.PNG

                And it's the same for every month.


                Thanks a lot again.