Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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] ;
Thank you very much Henric. Very helpful,
Can you also help in UI for the table as measure.
Thanks again in advance.
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).