topic Re: Cumulative Sum till another value is met and start fresh in App Development

Cumulative Sum till another value is met and start fresh

Mak3 — Sun, 13 Feb 2022 08:03:14 GMT

Hi QlikSense Friends,

Date	Work Hours	Fail Hours	Cumulative
01-01-2020	20		20
02-01-2020	16		36
03-01-2020	20		56
04-01-2020	11	4	67
05-01-2020	10		10
06-01-2020	12		22
07-01-2020	17		39
08-01-2020	19		58
09-01-2020	15	6	73
10-01-2020	19		19
11-01-2020	19		19

I need to have cumulative value as shown in Column Cumulative.

Every time when the system fail Hours are encountered, the next cumulative should reset as above.

Please advice.

Thanks in advance for your support

Mak

Re: Cumulative Sum till another value is met and start fresh

hic — Sun, 13 Feb 2022 16:35:01 GMT

Take a look at https://community.qlik.com/t5/Qlik-Design-Blog/Counters-in-the-Load/ba-p/1464117 - especially the last example using a combination of Peek() and RangeSum() to create accumulations in the script. Note also how "DaysWithTrend" is reset, the same way as you want.

Re: Cumulative Sum till another value is met and start fresh

Mak3 — Mon, 14 Feb 2022 04:27:39 GMT

Thanks Henric,

Appreciate your support. To recap,sharing the table

Date	Work Hours	Fail Hours	Cumulative
01-01-2020	20		20
02-01-2020	16		36
03-01-2020	20		56
04-01-2020	11	4	67
05-01-2020	10		10
06-01-2020	12		22
07-01-2020	17		39
08-01-2020	19		58
09-01-2020	15	6	73
10-01-2020	19		19
11-01-2020	19		38

the 1st three columns - Date,Work Hours,Fail Hours are as observed.

I am trying to create a table in which the 4th column Cumulative is cumulative sum of work hours till Fail Hours is greater than zero.

Thanks in advance

Re: Cumulative Sum till another value is met and start fresh

hic — Mon, 14 Feb 2022 10:35:36 GMT

Load
RangeSum([Work Hours],If(Peek([Fail Hours])>0,0,Peek(NewAccumulation))) as NewAccumulation,
*
Inline
[Date,Work Hours,Fail Hours,Cumulative
01-01-2020,20, ,20
02-01-2020,16, ,36
03-01-2020,20, ,56
04-01-2020,11,4,67
05-01-2020,10, ,10
06-01-2020,12, ,22
07-01-2020,17, ,39
08-01-2020,19, ,58
09-01-2020,15,6,73
10-01-2020,19, ,19
11-01-2020,19, ,38] ;

Re: Cumulative Sum till another value is met and start fresh

Mak3 — Mon, 14 Feb 2022 17:17:52 GMT

Thank you very much Henric. Very helpful,

Can you also help in UI for the table as measure.

Thanks again in advance.

Re: Cumulative Sum till another value is met and start fresh

hic — Tue, 15 Feb 2022 08:03:05 GMT

If you use Date as dimension, and sort it by Date, you could probably use

RangeSum(Sum([Work Hours]), If(Above(Sum([Fail Hours]))>0,0,Above(NewAccumulation)))

and name this measure NewAccumulation. Or rather - the name of the measure needs to be the same as the reference inside the measure.

Note that this is a recursive definition. This means that it will work fine if you have only a few rows (maybe a couple of hundreds or less) in the table, but it will not work if you have many rows (thousands).