Using a Cumulative Sum for Multiple Dimensions

[Sardaucar56]

Hello everyone,

I’m wondering about the use of the SumOver() function. I’m relatively new to using QuickSight, and I’m stuck on a requirement that seems simple at first glance.

I have a dataset structured as follows:

.

I want to display the cumulative sum for each day, with a time axis (hours and minutes). So far, I’ve managed to build a chart that looks like this:

.

However, when using SumOver(), I’m required to choose an aggregation. If I select the hour, I lose the cumulative calculation at the minute level. Conversely, if I choose the day, I lose the cumulative sum by hour.

I feel a bit lost and hope someone might have a clue that could point me in the right direction!

Thank you in advance for any insights!

[awsvig]

Hi @Sardaucar56 - Welcome to the community. Is there an example output graph that you intend to build? When you do sum over a period, you lose the granularity for that period as now you are looking at cumulative values. In your case, if you have minute level data, you can do a sum using minutes as the aggregation period and your plot will be at minute level.
If you want to display usage at granular level across days, then you may not need a sum function. Please find the screenshot below that shows granular data for all data points in your dataset across the two days. Are you looking for something else?

[Sardaucar56]

Hi @awsvig,

First, thanks a lot for this answer !

what i would like to achieve, is more something like that

But for now, it kinda merge the 2 lines everytime i try it :(.

[awsvig]

Hi @Sardaucar56 - Thanks for sharing the expected output. You can achieve this using the runningSum function. You can create a calculated field as shown below and then plot it with time on x-axis.

runningSum(
sum({sum(UseDatePerMinute)}),
[truncDate(“DD”,UseDay) ASC]
)

I was able to plot an example dataset using this.

[Sardaucar56]

Wow, thx a lot @awsvig, it’s so close to what i try to do !

But i notice (and i tried it), and the 2nd day do not start at 0, but get the cumulate of the previous day

futhermore, Calculated field did not let me use your expression as well, i had to adjust it like that :

runningSum(
sum(UseDateCountPerMinute),
[truncDate(“DD”, UseDay) ASC]
)

[awsvig]

Hi @Sardaucar56 – Oh, yes!.. Ok, if you want the count to reset every period of your choice, you can use periodToDateSumOverTime instead.

periodToDateSumOverTime(
sum({sum(UseDatePerMinute)}),
UseDay,
DAY
)

[Sardaucar56]

It’s seems to be the answers yes, i didn’t knew this function, perfect !

In you example, like in my test, i see that there is no cumulative sum per hours (for example, the row 10:20 of 11 feb with runningsum should be 192 and not 92).

i’m working on it too, maybe a format issue (i have this one for the datetime :
2024-11-01T00:00:00.000Z)