Show daily count and the total count up to that day in Quicksight

Question

I want to create a table analysis in AWS Quicksight that shows the number of new user per day and also the total number of user that has registered up until that day for the specified month.

The following sample table is what I want to achieve in Quicksight. It shows the daily register count for March:

+-----------+----------------------+----------------------+
|           | Daily Register Count | Total Register Count |
+-----------+----------------------+----------------------+
| March 1st |           2          |          42          |
+-----------+----------------------+----------------------+
| March 2nd |           5          |          47          |
+-----------+----------------------+----------------------+
| March 3rd |           3          |          50          |
+-----------+----------------------+----------------------+
| March 4th |           8          |          58          |
+-----------+----------------------+----------------------+
| March 5th |           2          |          60          |
+-----------+----------------------+----------------------+

The "Total Register Count" column above should show the total count of users registered from the beginning up until March 1st, and then for each row it should be incremented with the value from "Daily Register Count"

I'm absolutely scratching my head trying to implement the "Total Register Count". I have found some form of success using runningSum function however I need to be able to filter my dataset by month, and the runningSum function won't count the number outside of the filtered date.

My dataset is very simple, it looks like this:

+----+-------------+---------------+
| id | email       | registered_at |
+----+-------------+---------------+
| 1  | [email protected] | 2020-01-01    |
+----+-------------+---------------+
| 2  | [email protected] | 2020-01-01    |
+----+-------------+---------------+
| 3  | [email protected] | 2020-01-03    |
+----+-------------+---------------+
| 4  | [email protected] | 2020-01-04    |
+----+-------------+---------------+
| 5  | [email protected] | 2020-02-01    |
+----+-------------+---------------+

I hope someone can help me with this. Thank you!

Answer 1

I am new to QuickSight but the way I was able to get Total Register Count is by creating a calculated field called count and assigned it the fixed value of 1. Then I created a second calculated field "Total Register Count" with the following formula

runningSum(sum(count), [{ registered_at} ASC], [])

Answer 2

It sounds as if the CountOver function would work well for you. You'll need to partition your count by the day of the month (using the extract function). Here is a link related to the CountOver function.

https://docs.aws.amazon.com/quicksight/latest/user/countOver-function.html

This is called a Level Aware Aggregation in QuickSight. Here is additional information on that: https://docs.aws.amazon.com/quicksight/latest/user/level-aware-aggregations.html

Here is information on the extract function: https://docs.aws.amazon.com/quicksight/latest/user/extract-function.html

If I were to take a stab at your formula, it would look like this:

countover(ID,[extract('DD',registered_at)],PRE_FILTER)

Your table would have the registered_at field as the date.