amazon-quicksight

Running distinct count in quicksight

I want to implement a running distinct count with Amazon Quicksight. Here's an example of what that would look like:

Date ID Amount Running Distinct Count
1/1/1900 a 1 1
1/2/1900 a 3 1
1/2/1900 b 6 2
1/4/1900 a 3 2
1/8/1900 c 9 3
1/22/1900 d 2 4

I've tried runningSum(distinct_count, [Date ASC]), but this returns a sum of the distinct counts for each aggregated date field.

Can this be implemented in QuickSight?

Solution

  • You can use this workaround to get the same functionality as runningDistinctCount() as follows:

    runningSum(
  distinct_count(
    ifelse(
      datetime=minOver({Date}, [{ID}], PRE_AGG),
      {ID},
      NULL
    )),
  [{Date} ASC],
  []
)

    This would give you the runningDistinctCount of ID's over the Date field. It achieves it by considering just the first time the ID appears in the dataset, counting these and finally doing a runningSum on these counts.