Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnnymartinez
Contributor III
Contributor III

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:

example_ff.png

4 Replies
Anonymous
Not applicable

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.

johnnymartinez
Contributor III
Contributor III
Author

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 )  )

johnnymartinez
Contributor III
Contributor III
Author

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]

johnnymartinez
Contributor III
Contributor III
Author

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