Average of days difference between purchases (dynamic)
Hi,
I'm trying to build something that's a bit confusing, and maybe someone could help me out.
I know I can easily do this in script level, but I need to display an average of days between purchases of customers that will respond to date filters, e.g:
customer 1 have bought 6 times:
29/02/2024 - 02/01/2024 = 58 days diff
27/12/2023 - 25/12/2023 = 2 days diff
16/11/2023 - 12/06/2023 = 157 days diff
in this case, the average of days would be 72,3 days, considering I'm dividing 217 / 3 (returns).
If the user selects, for e.g, january, then the data should be something like:
null() - 02/01/2024 = null
27/12/2023 - 25/12/2023 = 2
16/11/2023 - 12/06/2023 = 157
In this scenario, the avg would be 79,5 days, given I'm not considering it's purchase on 29/02/2024 because of the filter selection
I know aggr() will build a "temporary" table for calculation, maybe I could achieve this using sort, aggr, max, min, idk...?
Without an appropriate adjusted data-model you will always need aggr() constructs to perform any multi-level aggregations.
I'm not sure if it would be possible respectively practically in your scenario to move the most of the logic into the data-model to get the wanted views with a single aggregation. Nevertheless you may try to calculate the date-differences with interrecord-functions, like:
load *, if(Customer = previous(Customer), Date - previous(Date)) as DateOffset resident Source order by Customer, Date;
Depending on your real data-set and requirements you may need some more conditions and/or another sorting and it may be further useful to calculate a running counter for the customer and/or with an extra even() wrapping and/or self-joining the min/max/count of dates and similar stuff.
I actually gave up this idea and worked it out in the data model by adding a "days difference" field with the calculation for each sale_id, based on the last sale_id respectively. Considering each sale_id have a date/calendar field, I can actually use avg() limited to the corresponding dates applied to the filter.
