Difference between two timestamps if some conditions are verified

[predicteam]

Hello guys,

I have the following dataset: 

I would like to have a KPI with the average value (in minute) of the difference between the two timestamps (task_date) with task_name 'check' and 'close'. The average value should be calculated once the data are aggregated for "acquisition_code".

In this example, my result should be 8 minutes, calculated as average value of 11 minutes and 5 minutes.

Please, could you help me in having a correct KPI formula?

Thanks

[BrunPierre]

Like this, perhaps.

=Interval(Avg(Aggr(Only({<task_name={'close'}>}task_date) - Only({<task_name={'check'}>}task_date),acquisition_code)),'m')

[predicteam]

Dear BrunPierre,

thank you so much!! It works great!

Just the last question: how could I change the formula to exclude from the calculation, for example, data with idTask = 1 and idTask = 2?

Thank you a lot again,

Marco

[BrunPierre · Accepted solution]

Do you want to exclude values 1 and 2 from idTask? Here's how...

=Interval(Avg({<idTask-={1,2}>}Aggr(Only({<task_name={'close'}>}task_date) - Only({<task_name={'check'}>}task_date),acquisition_code)),'m')

[predicteam]

Thanks you very very much! It works perfectly!