Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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]