4 Replies Latest reply: Jan 26, 2018 4:38 AM by John Martin

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

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:

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

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]

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]

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

and here's how it looks in Qlik :

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:

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:

'PRODUCT','SUM_TOTAL',

'APPLE','6.3',

'PEAR','48',

'ORANGE','1.5',

'CARROT','3.8',

'POTATO','8',

'PINEAPPLE','2.5'

];

[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:

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]

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

Has anyone successfully managed this yet, or is it best done in the SQL script instead?