PROBLEM WITH CUMULATIVE SUM IN EDITOR

[FEDE]

Hi everybody!

i hAVE THIS TABLE:

Date	TIME	TYPE	VALUE
01/01/2022	01:30	A	1
01/01/2022	02:30	A	3
01/01/2022	03:30	A	4
01/01/2022	04:30	A	5
01/01/2022	05:30	A	6
01/01/2022	06:30	A	4
02/01/2022	01:30	A	2
02/01/2022	02:30	A	2
02/01/2022	03:30	A	3
02/01/2022	04:30	A	1
02/01/2022	05:30	A	0
02/01/2022	06:30	A	1

I want the table with cumulative sum like this, but the sum have to restart from 0 everyday:

Date	TIME	TYPE	cumulative sum
01/01/2022	01:30	A	1
01/01/2022	02:30	A	4
01/01/2022	03:30	A	8
01/01/2022	04:30	A	13
01/01/2022	05:30	A	19
01/01/2022	06:30	A	23
02/01/2022	01:30	A	2
02/01/2022	02:30	A	4
02/01/2022	03:30	A	7
02/01/2022	04:30	A	8
02/01/2022	05:30	A	8
02/01/2022	06:30	A	9

the cumulative value on 02/01 restart from the first timestamp
Can anyone help me? 

i tried with:

LET lunghezza=NoOfRows(Date_CALENDAR);

for counter=0 to $(lunghezza)

Let MiaVariabile = Peek('Date_CALENDAR', $(counter),'calendario');

cumulato:

CONCATENATE

LOAD

RangeSum(peek('Cumulative') , STIMA) AS Cumulative,
TYPE AS tipo_cumulative,
Date AS GIORNO_CUMULATO,
TIME AS ORA_CUMULATO
resident ALGORITMO

where date#(Date,'YYYY-MM-DD')=date#('$(MiaVariabile)','YYYY-MM-DD')
;

next counter;

Thank You!

THANKS

[Oleg_Troyansky · Accepted solution]

Hi FEDE,

You can achieve this accumulation in a single load. The key is to sort the data properly and to check the previous keys (in your case, Date and maybe Type?). For example, just with the date:

LOAD
...,
RangeSum(STIMA, 
                        IF(Previous(Date) <> Date, 0, Peek('Cumulative')))   as Cumulative

FROM

...

ORDER BY

     Date

;

I'm describing these cumulative techniques with a lot of details in my book - check it out!

Also, to learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[FEDE]

Thak You Oleg, i'm gonna buy your Book!!