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?