Weekly Cumulative Sum by Course with Date Filters & Correct Totals/Subtotals

Question & Answer
,
1

Continuing the discussion from How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?:

Hello everyone,

I’m trying to create a table in QuickSight that displays a cumulative sum of the number of students by week and by course. There are a few requirements:

  1. The table can be filtered by course (potentially multiple courses) and by date range.
  2. The cumulative sum must be aware of events before the selected date range, so it doesn’t start from zero if there were students in previous weeks.
  3. The totals and subtotals should still work correctly, reflecting the proper cumulative values for the entire filtered period.

I’ve already tried using the
minOver(min({order date}), [{order date}])
trick to handle inclusive ranges, and my current running sum calculation looks like this:

runningSum(
  sum({n_paying_students}),
  [{merge_date_week_start} ASC],
  [{true_course_name}]
)

However totals and subtotals are not really accurate.

immagine
immagine1027×917 84.6 KB

Has anyone encountered this requirement or come up with a solution for including the data prior to the filter range?
I’m looking for suggestions on the best way to configure this in QuickSight so the totals and subtotals are accurate.

Thank you in advance for any guidance!

2

Hi @angelicamarrone and welcome to the QuickSight community!
Looking at your example, the ‘students’ and ‘students cumsum’ columns seem to be adding correctly. What is incorrect about the way they are totaling?

3

Hi @angelicamarrone,
Following up here as it’s been awhile since we last heard from you; did you have any additional questions regarding your post or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi!
I found a work around, thank you so much anyway!
Do I need to close it?

1 Like