Best way to group by date for a distinct_countIF in Quicksight Calculated Fields

Question & Answer
, , ,
1

I am trying to calculate a metric PDAU which is defined by the distinct count of users who have made a purchase on or before the active date, and avg PDAU is the average of these daily values over a date range. I have created the following calculated field for PDAU:

distinct_countIf(uidn, {first_purchase}<={active_date})

This works only when I look at something like a timeseries plot because the plot would be grouped by day, so the daily counts are correct. However, I need to also be able to find the average daily PDAU which requires me to group the above calculated field by date in order to take the average. But distinct_countIf is not a level-aware calculation, so I am unsure how to do something like this.

I have tried adding a binary field to my dataset to indicate if a user meets this criteria, however my dataset has a lot of fields which are required because our users often log in with different devices, and things of this nature. This means that the unique user identifier is often repeated multiple times in the same day so summing this binary field leads to an over inflated user count. I need to be able to count distinct user id.

Are there any functions I am able to use that would help me group this type of countIF field by date so that I am able to take the average without running into the ‘Nesting of aggregate functions’ error?

1 Like
2

I might try this.

distinctCountOver(ifelse({first_purchase}<={active_date},{uidn},NULL), [{date}],PRE_AGG)

Then you can group this ^

3

This is a great suggestion, I tried this and the calculated field saved successfully, but then when I try to apply it to a visual I get a SQL Exception error:

Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.

I wonder if this process is too resource intensive. I have tried with different types of visuals and keep getting the same error. My other visuals work so I don’t think it has to do with connection to the database

4

Hello @jovanaV, sorry for the delayed response! If you still need assistance with this question, or if you have a new QuickSight question, please post a new topic in the community. You will be at the top of the priority list for a response from one of our QuickSight Experts.