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

johnnymartinez — Fri, 22 Sep 2017 13:27:48 GMT

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]

Anonymous — Fri, 22 Sep 2017 14:20:17 GMT

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]

johnnymartinez — Fri, 22 Sep 2017 14:25:40 GMT

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]

johnnymartinez — Mon, 02 Oct 2017 12:55:15 GMT

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:

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]

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

johnnymartinez — Fri, 26 Jan 2018 09:38:17 GMT

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

topic Re: Sum row by date range on Load Script? [QlikSense] in App Development

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

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

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

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

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