# Running Sum - Sum over 12 months but starting with daily-aggregated data

Hi all,

I am getting stuck with a quite basic task: I want to calculate the 12-month running sum for a few metrics, starting from data aggregated daily. I ran some tests using windowSum but I cannot crack the case.

The original data is more or less in this format:

Date User Transaction Amount
2024-01-01 abc Sell 200
2024-01-04 abc Sell 20
2024-01-04 def Sell 10
2024-01-09 def Sell 50

So daily aggregated data by user and transaction type.

I want to get the running sum so that at 2024-07-04 it will return the total for the user starting from 2024-07-04. Something like this:

Date User Transaction Amount
2024-01-01 abc Sell 200
2024-01-04 abc Sell 220
2024-01-04 def Sell 10
2024-01-09 def Sell 60

Going back 12 months per each user.

As you can see, in my data there are days in which a user has no transactions, so what I need to base my calculation is the date. In SQL I would use a simple SUM (OVER) windows function.

In this instance I thought that I could use windowSum, like this:

``````windowSum(sumIf({Amount}, {Transaction} = "Buy"),[truncDate("MM", {Date}) ASC],12,0)
``````

But this won’t do the trick.
One reason, I guess, is that I would need something like `addDateTime` to get the relevant dates, but I cannot figure out how to get there.

I also looked into `runningSum` but still, cannot figure out how to make the calculation work for the last 12 months. Also considering that I do not want calendar months, i.e. if I am at the 15th of July 2024 then I want to start calculating from the 15th of July 2023, and not just truncate the date at month level, then going from full July 2023 to 15th of July 2024.

The older posts and documentation didn’t help me much (mostly refer to starting from the same level of time granularity), so I’d like to ask if anyone can point out to the right direction here.

Thanks!

Hi @Massi, Amazon QuickSight does not have a direct built-in function specifically named for calculating a 12-month running sum over daily aggregated data. However, you can achieve this using a combination of `windowSum` and `addDateTime` functions.

Here’s a high-level approach:

### Step 1: Create a Calculated Field for the Date Filter

First, create a calculated field to filter the data to include only the last 12 months from each date.

1. Create a calculated field to determine if a date is within the last 12 months:

Pseudocode (Syntax may vary)

``````isWithinLast12Months = ifelse(
1,
0
)
``````

### Step 2: Calculate the Running Sum

Use the `windowSum` function to calculate the running sum. Note that QuickSight’s `windowSum` can take an optional third parameter to specify a frame, but it does not natively support specifying a rolling 12-month window directly. Instead, we use a workaround by filtering the dataset first.

1. Create a calculated field for the running sum:

Pseudocode (Syntax may vary)

``````RunningSum = windowSum(sum({Amount}), [{Date} ASC])
``````

### Step 3: Apply the Calculations

1. Filter your analysis to include only data within the last 12 months using the calculated field `isWithinLast12Months`:
• Set a filter to include rows where `isWithinLast12Months` equals 1.
1. Add the `RunningSum` calculated field to your visual:
• This field will now represent the running sum of the `Amount` over the specified date range.

### Example in Practice

Here’s how you would implement this in QuickSight:

1. Create the Calculated Field for Filtering Dates:
• Go to your dataset and create a new calculated field named `isWithinLast12Months`:

Pseudocode (Syntax may vary)

``````ifelse({Date} >= addDateTime(-12, 'MM', {Date}), 1, 0)
``````
1. Create the Calculated Field for the Running Sum:
• Create another calculated field named `RunningSum`:

Pseudocode (Syntax may vary)

``````windowSum(sum({Amount}), [{Date} ASC])
``````
1. Apply the Date Filter in Your Analysis:
• In your analysis, add a filter using the `isWithinLast12Months` field and set it to 1.
1. Use the Running Sum in Your Visual:
• Add the `RunningSum` field to your visual to see the running sum of the `Amount`.

• Continuous Dates: Ensure that your dataset has continuous dates. If there are gaps, you may need to fill them in your data source to get an accurate running sum.
• Performance: Filtering a large dataset to the last 12 months may have performance implications. Consider pre-aggregating data if needed.

Also, here are different approaches - How can I make a formula that counts me in a 12 months rolling? - #7 by KLIKIN

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

Hi @Xclipse,

First of all thank you for taking the time to look into this!
I am not sure I understand though, when you mention the formula isWithinLast12Months:

`ifelse({Date} >= addDateTime(-12, 'MM', {Date}), 1, 0)`

As you can see I have a Date field with daily data (which is sometimes missing as a customer could have no activity on a specific day), and your formula seems to be “if the date is equal or greater to the date then return 1, otherwise 0”, but you’re checking the date against itself, or am I missing something?
In this case it would always return one.

Sorry if I misunderstood something and thank you again.

Can we have a default date for blanks/null in the SQL or dataset? That would help with the calculation.

The problem is that there is no fixed date we can use.
Sorry, maybe I haven’t been clear: what I want to achieve is for each specific date to have the sum for the previous transactions made at that date minus 12 months.

To go back to my previous example, trying to make it clearer:

Date User Transaction Amount
2023-01-01 abc Sell 200
2023-03-04 abc Sell 20
2023-04-05 abc Sell 200
2023-06-02 abc Sell 10
2024-01-04 def Sell 140
2024-01-09 def Sell 50

For the sake of simplicity I am only interested into the “Buy” type for user “abc”.
What I want to achieve is this table:

Date User Transaction Amount runningSum

At any date in which the user has a transaction, I show the total for the previous 12 months up to that point.

In SQL I would use something like this:

``````SELECT
Date,
User,
SUM(Amount) OVER (PARTITION BY User ORDER BY Date
RANGE BETWEEN INTERVAL '12 Months' PRECEDING AND CURRENT ROW) AS runningSum
FROM myTable
``````

Thank you again!

Hi @Massi, thanks for the detailed explanation, try this

``````periodOverPeriodLastValue(
sum({Amount}),
{Date}
)
``````

Also, review this function:

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena)

Hi Xclipse, I am getting the error that the function should’ve two arguments instead of three.
I looked into the function but I am not sure I understand how a PoP could provide what I am looking for, but even trying to save it, it’s not working.

The runningSum is one of the first functions I looked at - together with windowSum - but I could not find a way to look only into the last 12 months of activity.

I created a dataset and analysis, and the shared dashboard is here:
runningSum Demo (Q&A 32216)