Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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!
Thak You Oleg, i'm gonna buy your Book!!