Hello,
I need help understanding if what I want to achieve is possible. I have dates for which I run a daily running sum, and then want to aggregate it to weeks/months/years while keeping the total from each day to be added to the aggregated sum - which would be easier to show with examples.
The data I have is in this format (importantly, I am missing some days):
| date | value | running sum |
|---|---|---|
| 01.01.2025 | 1 | 1 |
| 03.01.2025 | 1 | 2 |
| 06.01.2025 | 2 | 4 |
| 09.01.2025 | 1 | 5 |
| 12.01.2025 | 1 | 6 |
| 20.01.2025 | 1 | 7 |
| 24.01.2025 | 3 | 10 |
What I can achieve in QS is that when aggregating, I get the sum from a given week, and then the running sum is calculated, which gives me a lower total than daily calculations.
| Date | value | Running sum daily | Running sum weekly | Running sum monthly |
|---|---|---|---|---|
| 01.01.2025 | 1 | 1 | ||
| 02.01.2025 | 1 | |||
| 03.01.2025 | 1 | 2 | ||
| 04.01.2025 | 2 | |||
| 05.01.2025 | 2 | |||
| 06.01.2025 | 2 | 4 | ||
| 07.01.2025 | 4 | 4 | ||
| 08.01.2025 | 4 | |||
| 09.01.2025 | 1 | 5 | ||
| 10.01.2025 | 5 | |||
| 11.01.2025 | 5 | |||
| 12.01.2025 | 1 | 6 | ||
| 13.01.2025 | 6 | |||
| 14.01.2025 | 6 | 6 | ||
| 15.01.2025 | 6 | |||
| 16.01.2025 | 6 | |||
| 17.01.2025 | 6 | |||
| 18.01.2025 | 6 | |||
| 19.01.2025 | 6 | |||
| 20.01.2025 | 1 | 7 | ||
| 21.01.2025 | 7 | 7 | ||
| 22.01.2025 | 7 | |||
| 23.01.2025 | 7 | |||
| 24.01.2025 | 3 | 10 | ||
| 25.01.2025 | 10 | |||
| 26.01.2025 | 10 | |||
| 27.01.2025 | 10 | |||
| 28.01.2025 | 10 | 10 | ||
| 30.01.2025 | 10 | 10 | 10 |
What I would like to achieve is something like this - basically, I want the aggregation to sum the total of each day:
| Date | value | Running sum daily | Running sum agreagte over week | Running sum agregate over month |
|---|---|---|---|---|
| 01.01.2025 | 1 | 1 | ||
| 02.01.2025 | 1 | |||
| 03.01.2025 | 1 | 2 | ||
| 04.01.2025 | 2 | |||
| 05.01.2025 | 2 | |||
| 06.01.2025 | 2 | 4 | ||
| 07.01.2025 | 4 | 16 | ||
| 08.01.2025 | 4 | |||
| 09.01.2025 | 1 | 5 | ||
| 10.01.2025 | 5 | |||
| 11.01.2025 | 5 | |||
| 12.01.2025 | 1 | 6 | ||
| 13.01.2025 | 6 | |||
| 14.01.2025 | 6 | 53 | ||
| 15.01.2025 | 6 | |||
| 16.01.2025 | 6 | |||
| 17.01.2025 | 6 | |||
| 18.01.2025 | 6 | |||
| 19.01.2025 | 6 | |||
| 20.01.2025 | 1 | 7 | ||
| 21.01.2025 | 7 | 97 | ||
| 22.01.2025 | 7 | |||
| 23.01.2025 | 7 | |||
| 24.01.2025 | 3 | 10 | ||
| 25.01.2025 | 10 | |||
| 26.01.2025 | 10 | |||
| 27.01.2025 | 10 | |||
| 28.01.2025 | 10 | 161 | ||
| 30.01.2025 | 10 | 171 | 498 |
I’ve tried many iterations of functions including running sum, sum over time, etc., and couldn’t get it working. Is this somehow possible to do in QS?