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

Question & Answer
, ,
1

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 Buy 100
2024-01-01 abc Sell 200
2024-01-02 abc Buy 50
2024-01-04 abc Sell 20
2024-01-01 def Buy 20
2024-01-04 def Sell 10
2024-01-05 def Buy 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 Buy 100
2024-01-01 abc Sell 200
2024-01-02 abc Buy 150
2024-01-04 abc Sell 220
2024-01-01 def Buy 20
2024-01-04 def Sell 10
2024-01-05 def Buy 30
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!

2

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(
    {Date} >= addDateTime(-12, 'MM', {Date}),
    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.

Additional Notes

  • 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!

3

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.

4

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

5

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 Buy 100
2023-01-01 abc Sell 200
2023-01-02 abc Buy 50
2023-03-04 abc Sell 20
2023-04-01 abc Buy 100
2023-04-05 abc Sell 200
2023-06-02 abc Buy 80
2023-06-02 abc Sell 10
2023-10-07 abc Buy 90
2023-11-09 abc Buy 250
2024-01-01 abc Buy 40
2024-01-01 abc Buy 20
2024-01-02 abc Buy 30
2024-02-12 abc Buy 100
2024-04-04 abc Buy 20
2024-01-01 def Buy 20
2024-01-04 def Sell 140
2024-01-05 def Buy 10
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
01/01/2023 abc Buy 100 100
02/01/2023 abc Buy 50 150
01/04/2023 abc Buy 100 250
02/06/2023 abc Buy 80 330
07/10/2023 abc Buy 90 420
09/11/2023 abc Buy 250 670
01/01/2024 abc Buy 40 710
01/01/2024 abc Buy 20 730
02/01/2024 abc Buy 30 660
12/02/2024 abc Buy 100 710
04/04/2024 abc Buy 20 630

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
WHERE Transaction = 'Buy'

Thank you again!

6

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

periodOverPeriodLastValue(
    sum({Amount}),
    addDateTime(-1, 'YYYY', {Date}),
    {Date}
)

Also, review this function:

runningSum

7

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)

8

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)

9

Hi @Massi, besides using custom SQL, multiple datasets/alias (parent-child) – I’m out of ideas.

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!