I’m trying to build a KPI that tells me how many customers have returned within a year of any of their appointments. customer_id and datetime are the relevant fields.
What I WANT to do is:
DaysSincePreviousApptDate: dateDiff(lag(datetime, [datetime ASC], 1, [{customer_id}]), max(datetime))
distinct_countIf({customer_id}, {DaysSincePreviousApptDate} <= 365)
but I get:
Nesting of aggregate functions like DISTINCT_COUNT and COUNT(DISTINCT CASE WHEN DATEDIFF(LAG(“datetime”, “datetime”, 1, “customer_id”), MAX(“datetime”)) <= 365 THEN “customer_id” ELSE NULL END) is not allowed.
I need a distinct count of the customer_id where ANY pair of datetime is less than a year. Any thoughts?
It did not…
I have a table that has “customer_id” and “datetime” and I want to get a distinct count of all the customer_id that have any two rows with <= 365 days between them and use that value in a calculated field divided by the total number of customers so that I can display “how many of our customers have ever returned within a year” in a KPI.
This gives me the data that I need if I want to add it to a table:
periodToDateCountOverTime(count({customer_id}), datetime, YEAR) - 1
but I want something like this so I can use it to show the percentage calculation:
distinct_countIf({customer_id}, periodToDateCountOverTime(count({customer_id}), datetime, YEAR) > 1)
Hi @CCTG-Jason, the challenge in this use case is that QuickSight doesn’t support aggregation after lag function. One workaround is to implement the lag in the custom SQL of your dataset, and add it as a field to the dataset, then you can calculate datediff() and distinct_countif() in the analysis level calculated field.
We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.”
1 Like
