topic Re: Average of days difference between purchases (dynamic) in App Development

Average of days difference between purchases (dynamic)

pedrohenriqueperna — Fri, 05 Apr 2024 14:44:49 GMT

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...?

Any help is appreciate.

Re: Average of days difference between purchases (dynamic)

marcus_sommer — Mon, 08 Apr 2024 14:16:14 GMT

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.

Re: Average of days difference between purchases (dynamic)

pedrohenriqueperna — Mon, 08 Apr 2024 14:44:28 GMT

Hi, Marcus

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.

Anyways, thanks for the reply!!