Context: I have an dataset of Orders in quicksight, that is joined with Customer data. Because a Customer can have more than one Order the Customer data is duplicated and needs to be deduped.
I need to do an analysis of the number of days it took from the Customer to register (customer_date_added) to their first order (min(order_date_added)) and then bucket/group the results based on customer_date_added with different granularities of the date.
This is what I currently have working (6 visuals corresponding to the built-in date granularities in quicksight: year, quarter, month, week, day, hour)
*Visual 1* - x-axis:customer_date_added (aggregate year), y-axis:
ifelse(dateDiff(customer_date_added, min(order_date_added)) < 365, 1, 0)
*Visual 2* - x-axis:customer_date_added (aggregate quarter), y-axis:
ifelse(dateDiff(customer_date_added, min(order_date_added)) < 90, 1, 0)
*Visual 3* - x-axis:customer_date_added (aggregate month), y-axis:
ifelse(dateDiff(customer_date_added, min(order_date_added)) < 30, 1, 0)
*Visual 4* - x-axis:customer_date_added (aggregate week), y-axis:
ifelse(dateDiff(customer_date_added, min(order_date_added)) < 7, 1, 0)
.... etc
Issue: I have a lot of these dateDiff with bucketing visuals (not just days to first order) so my field list is getting overrun with fields like customer_first_order_within_year, customer_first_order_within_quarter, customer_first_order_within_month, etc. Also rather than just have one visual where I can change the date_granularity from year to month I have to maintain 6 different visuals for each dateDiff analysis I want to to.
What I want: One visual with two calculated fields (new_customer_with_first_order, new_customer_no_first_order). When I change the data granularity on customer_date_added to a new date granularity, the new_customer_with_first_order automatically displays customers that were added in that time period AND with a first order within that time period. Conversely new_customer_no_first_order would display customers that were added in that time period AND did not have an order within that time period. new_customer_with_first_order + new_customer_no_first_order = new customers within that time period.
What I have tried: I am relatively new to quicksight and business intelligence tools, but have a pretty good grasp of SQL. I have tried to read up on Level Aware Aggregation, Window Functions. None of these give examples using dates or date granularity. This seems like it would be a popular type of analysis but maybe it's not possible yet?
https://stackoverflow.com/a/63064505/3412268
https://docs.aws.amazon.com/quicksight/latest/user/windowMax-function.html
https://docs.aws.amazon.com/quicksight/latest/user/countOver-function.html
https://docs.aws.amazon.com/quicksight/latest/user/level-aware-aggregations.html