3 Replies Latest reply: Oct 2, 2017 8:55 AM by John Martin RSS

    Sum row by date range on Load Script? [QlikSense]

    John Martin

      Is there a way to do this?

       

       

      Basically, add a column in the load script showing a sum of balance over current or previous dates per row?


      In Excel, it'd be the formula in green, where it's the sum of the rows where the date is less/equal to the current cursor date:

      example_ff.png

        • Re: Sum row by date range on Load Script? [QlikSense]
          Bill Markham

          Maybe use a Pivot Table in the front end and exploit subtotals.

           

          You could use Group By and Sum() in the load script, but that may well not be appropriate in your use case.

            • Re: Sum row by date range on Load Script? [QlikSense]
              John Martin

              Hi Bill, thanks for the reply,  The reason I'm trying to set it as static data in the load script, is that I have ~ 5-6 million rows , and didn't want to overly burden the front end dynamically calculating these figures.

               

              Is there a syntax for dimension from current cursor position vs overall range ?

               

              Like :  ( Sum ( if ( [date] < Current([date]), Amount )  )

            • Re: Sum row by date range on Load Script? [QlikSense]
              John Martin

              The running sum doens't appear to be picking up the prior amounts - for example - here's the chart in excel :

               

              r_sum2.png

              and here's how it looks in Qlik :

               

              r_sum.png

               

              Here's the code I am using, none seems to arrive at the totals I brought in as a static table from Excel.

               

              I want to have separate running sums for each fruit, by date.

               

              I've tried doing it by date and by row numbers.

               

               

              [CODE]

               

               

               

              fruit:

              Load

              date#(DATE,'DD/MM/YYYY') as [F_DATE],AMOUNT,PRODUCT,QTY,ROWNO() AS 'RW_NUM'

              Inline

              [

              'DATE','AMOUNT','PRODUCT','QTY','RSUM','PROD_SUM','DATE_BALANCE',

              '14/01/2017','3.2','PEAR','4','3.2','3.2','4.6',

              '14/01/2017','0.9','APPLE','2','4.1','0.9','4.6',

              '14/01/2017','0.1','CARROT','1','4.2','0.5','4.6',

              '14/01/2017','0.4','CARROT','4','4.6','0.5','4.6',

              '15/01/2017','0.9','APPLE','2','5.5','1.8','13',

              '15/01/2017','4.8','PEAR','6','10.3','9.6','13',

              '15/01/2017','0.5','ORANGE','2','10.8','0.5','13',

              '15/01/2017','1.6','PEAR','2','12.4','9.6','13',

              '15/01/2017','0.6','CARROT','6','13','1.1','13',

              '16/01/2017','3','POTATO','3','16','3','16',

              '17/01/2017','0.9','APPLE','2','16.9','2.7','20.1',

              '17/01/2017','3.2','PEAR','4','20.1','12.8','20.1',

              '18/01/2017','8.8','PEAR','11','28.9','21.6','28.9',

              '19/01/2017','0.45','APPLE','1','29.35','3.15','29.65',

              '19/01/2017','0.3','CARROT','3','29.65','1.4','29.65',

              '20/01/2017','0.25','ORANGE','1','29.9','0.75','34.3',

              '20/01/2017','0.4','CARROT','4','30.3','1.8','34.3',

              '20/01/2017','4','PEAR','5','34.3','25.6','34.3',

              '22/01/2017','0.1','CARROT','1','34.4','1.9','35.4',

              '22/01/2017','1','POTATO','1','35.4','4','35.4',

              '23/01/2017','0.9','APPLE','2','36.3','4.05','36.3',

              '25/01/2017','2.5','PINEAPPLE','1','38.8','2.5','38.8',

              '27/01/2017','4.8','PEAR','6','43.6','30.4','45',

              '27/01/2017','0.9','APPLE','2','44.5','4.95','45',

              '27/01/2017','0.5','ORANGE','2','45','1.25','45',

              '29/01/2017','0.1','CARROT','1','45.1','2','45.1',

              '31/01/2017','0.4','CARROT','4','45.5','2.4','45.5',

              '01/02/2017','3','POTATO','3','48.5','7','48.5',

              '02/02/2017','1.6','PEAR','2','50.1','32','50.7',

              '02/02/2017','0.6','CARROT','6','50.7','3','50.7',

              '03/02/2017','0.9','APPLE','2','51.6','5.85','51.6',

              '05/02/2017','3.2','PEAR','4','54.8','35.2','54.8',

              '07/02/2017','8.8','PEAR','11','63.6','44','63.6',

              '08/02/2017','0.3','CARROT','3','63.9','3.8','68.4',

              '08/02/2017','0.4','CARROT','4','64.3','3.8','68.4',

              '08/02/2017','4','PEAR','5','68.3','48','68.4',

              '08/02/2017','0.1','CARROT','1','68.4','3.8','68.4',

              '09/02/2017','0.45','APPLE','1','68.85','6.3','70.1',

              '09/02/2017','0.25','ORANGE','1','69.1','1.5','70.1',

              '09/02/2017','1','POTATO','1','70.1','8','70.1'

              ];

               

              POSI:

               

               

              Load * Inline [

              'PRODUCT','SUM_TOTAL',

              'APPLE','6.3',

              'PEAR','48',

              'ORANGE','1.5',

              'CARROT','3.8',

              'POTATO','8',

              'PINEAPPLE','2.5'

               

               

              ];

               

               

              [RSUM_ADD]:

               

              LOAD

              [F_DATE],num#([F_DATE]) as 'numdate',PRODUCT,

              IF(PEEK(PRODUCT)=PRODUCT,PEEK(RSUM_Q)+QTY,QTY) AS RSUM_Q,

              IF(PEEK(PRODUCT)=PRODUCT,PEEK(RSUM_A)+AMOUNT,AMOUNT) AS RSUM_A

               

               

              RESIDENT fruit

              ORDER BY [F_DATE] DESC ,AMOUNT,PRODUCT,QTY;

               

               

              TEST:

              LOAD

                  PRODUCT,

                  [F_DATE],

                  RangeSum (SUM(IF(PEEK(PRODUCT)=PRODUCT,AMOUNT))) AS 'RSUM_AMT_RNUM',

                  RANGESUM(Sum(IF(PEEK(PRODUCT)=PRODUCT,AMOUNT)) + SUM(IF(PEEK(PRODUCT)=PRODUCT,PEEK(Cumulative_AMT),0))) AS Cumulative_AMT,

                  RANGESUM(Sum(IF(PEEK(PRODUCT)=PRODUCT,QTY)) + SUM(IF(PEEK(PRODUCT)=PRODUCT,PEEK(Cumulative_QTY),0)))   AS Cumulative_QTY

              Resident fruit

               

               

              GROUP BY  PRODUCT, [F_DATE]

              ORDER BY [F_DATE] DESC ,AMOUNT,PRODUCT,QTY;

               

              [/CODE]