4 Replies Latest reply: Feb 5, 2018 11:37 AM by Pim van der Kolk RSS

    Sum of current period vs sum of previous period

    Pim van der Kolk

      Dear Qlikkers,


      In the attachment you find a list with 0's and 1's of a customer (Cust). I'm facing difficulties with calculating the amount of 1's of the new period versus the amount of 1's of the previous period.


      So in every period I need to count all the new 1's. In the rows 29 till 31 of the Excel file I'm summing up the amount of the last created period (the column far right, row 29 in Excel) and I take the difference of the other columns with the total of the columns of the previous period (row 30).


      Below I've added my main formulas, which have worked until the start of the new year:


      sum([P1]) AS Per1,

      sum([P2]) AS Per2,

          .           .           .

          .           .           .

          .           .           .

      sum([P12]) AS Per12

      sum([P1-2018]) AS Per13

      Group by Cust,Period,Year;





      LOAD *,

      If(Cust=Previous(Cust),Previous(Totaal),0) As TotalPP;

      LOAD *,

      (P1+P2+P3+P4+P5+P6+P7+P8+P9+P10+P11+P12) AS Total

      Order by Cust,Period;

      I guess the problem is the fact that I don't work with a Master Calendar this time and therefore Qlik doesn't read P1-2018 as the successor of P12 (I've tried it with P12-2017 already, but no luck)? Can you help me with this problem? I can see a Master Calendar would be more convenient. Since I only have periods instead of exact dates, can we solve this without using this calendar? It seems like an easy problem, but I can't make it work. Thanks a lot in advance!