Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

johnnymartinez
New 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

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.

johnnymartinez
New Contributor III

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

johnnymartinez
New Contributor III

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 :

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
New Contributor III

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?

Community Browser