topic Re: Sum based on previous data in QlikView

Sum based on previous data

Kwame — Mon, 28 Mar 2022 10:32:04 GMT

Hi,

I want to sum using previous data. That is adding the previous Daily interest to the Interest for each current day.

So, 112.83 + 0.79 = 113.62

Any ideas guys please?

Sample

Transaction Date	Transaction ID	Interest	Daily Interest
28/03/2022	1115700004	112.83	0.79

Desired Result

Transaction Date	Transaction ID	Interest	Daily Interest
29/03/2022	1115700004	113.62	0.72

Re: Sum based on previous data

dmac1971 — Mon, 28 Mar 2022 11:30:12 GMT

Yes you should be able to do this at load using the Peek function :

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/InterRecordFunctions/Peek.htm

Re: Sum based on previous data

MarcoWedel — Mon, 28 Mar 2022 19:59:39 GMT

one solution might be:

table1: LOAD TransactionDate, TransactionID, DailyInterest, RangeSum(Peek(Interest),Previous(DailyInterest)) as Interest Inline [ TransactionDate,TransactionID,DailyInterest 27/03/2022,1115700004,112.83 28/03/2022,1115700004,0.79 29/03/2022,1115700004,0.72 30/03/2022,1115700004,0.71 31/03/2022,1115700004,0.75 01/04/2022,1115700004 ];

hope this helps

Marco

Re: Sum based on previous data

Kwame — Wed, 06 Apr 2022 10:49:21 GMT

Hi @MarcoWedel

Just out of curiosity, how would this script "RangeSum(Peek(Interest),Previous(DailyInterest))" be translated into an expression in a chart type, Many thanks.

Re: Sum based on previous data

dmac1971 — Wed, 06 Apr 2022 10:50:23 GMT

@MarcoWedel look at https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/InterRecordFunctions/above.htm it should help.