How to calculate the average date difference between current and next record

[kiko]

I have a dataset like this:

I want to calculate the average days between each sale by category. I have calculated the next sale day by sale category and person as

Date diff between sale date and next sale date as

Avg days between sales by category as

The avg can be shown in the table format

However, I want to show the average sale days by category in a table format or as KPI, without extra person and detailed date columns, something like:

When I remove the extra columns, there’s an error of Table calculation references missing

Is there anyway to calculate it on an aggregated level? I do not want to calculate it in the datasets because there are some filters I want to add to the calculation.

Thanks!

[debapc]

Hi @kiko - I think you can achieve this using the following steps:

1. You can add another calculated field as:
   c_rank_sale_date = rank([min({sale_date}) ASC], [{sale_category}])

2. Now you can add a new table with following fields: sale_category, sale_date, sale_person and c_avg_sale_diff.

3. You can hide sale_date and sale_person fields.

4. Add a filter to this new table. Choose c_rank_sale_date. Use the filter condition equals to 1. This will only show one value of “In Person” and “Virtual” avg sale diff.

Let us know if this worked for you. Thanks!

[kiko]

Hi @debapc Thank you for the solution! It works in the sample dataset! I tried it on the real dataset, if several people have the same min sale date, multiple records will still be shown in the table though.
i.e. when the min sale_date is the same for one sale category in the example below

, then QS will have two records for In Person sale_category since there are two records for the the min sale_date

[debapc]

Thanks @kiko for getting back on this. You can make the following changes:

1. Create a calculated field: c_sale_person_date_concat = concat(toString({sale_date}),{sale_person})

2. Change the “c_rank_sale_date” calculation as rank([{c_sale_person_date_concat} ASC],[{sale_category}])

3. The new table that you created, should have the following fields: sale_category, sale_date, sale_person, c_sale_person_date_concat and c_avg_sale_diff. Hide sale_date, sale_person and c_sale_person_date_concat fields.

4. Keep c_rank_sale_date as the filter to this table. Use the filter condition equals to 1. This will only show one value of “In Person” and “Virtual” avg sale diff.

Let us know if this works. Thanks!

[kiko]

Hi @debapc The solution works! Appreciate your help!

[debapc]

Great, thanks @kiko for the update!