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

Q&A
, ,
2

Hi @Massi, Amazon Quick Sight 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 Quick Sight’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 Quick Sight:

  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 Quick Sight Community!