3

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  | aaa@aaa.com | 2020-01-01    |
+----+-------------+---------------+
| 2  | bbb@aaa.com | 2020-01-01    |
+----+-------------+---------------+
| 3  | ccc@aaa.com | 2020-01-03    |
+----+-------------+---------------+
| 4  | abc@aaa.com | 2020-01-04    |
+----+-------------+---------------+
| 5  | def@bbb.com | 2020-02-01    |
+----+-------------+---------------+

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

Wisnu Shin
  • 81
  • 3
  • 6

2 Answers2

0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
DataNut
  • 317
  • 1
  • 7
  • Hi, thank you for answering! appreciate it a lot. I assume `countOver` is used to get the value for the "Daily Register Count" right? what I'm struggling right now is showing the "Total Register Count" column. I need to show the total number of registered user by the day as my table above shows. For example, by March 1st 42 users has registered in total, and since 5 user registered in March 2nd, the total register count for March 2nd will be 47 (42 + 5). Can I somehow use `countOver` to achieve this? Thanks! – Wisnu Shin Jul 02 '20 at 02:00
0

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], [])

enter image description here

chrigu
  • 58
  • 1
  • 5