Calculate average lead time

[Anonymous]

Hi!

I have got a table similar to the format below in which I need to calculate the average lead time (finish date - start date) in a certain period of time (activities finished in the last 7 days, in the last 14 days and so on).

ID	START DATE	FINISH DATE
1	30/10/2017	06/11/2017
2	01/11/2017	08/11/2017
2	01/11/2017	08/11/2017
3	03/11/2017	07/11/2017
4	03/11/2017
5	05/11/2017	10/11/2017
5	05/11/2017	10/11/2017

Please note 2 points:

• the id might repeat
• only the ids already finished (finish date is not null) should be taken into account

Can someone help me?

[sunny_talwar]

May be this

Avg({<[FINISH DATE] = {'*'}>} Aggr([FINISH DATE] - [START DATE], ID))

[Anonymous]

Sunny, thks for the answer! What if I wanted to set a time variable and calculated the average of all the activities that finished after that variable?

E.G: FINISH DATE >= 01/01/2018

[sunny_talwar]

May be this

Avg({<[FINISH DATE] = {"$(=Date(MakeDate(2018, 1, 1), 'DD/MM/YYYY'))"}>} Aggr([FINISH DATE] - [START DATE], ID))

[Anonymous]

Sorry for insisting, but does it mean that all the IDs with finish date after this date would be taken into account? If it doesn´t, how can we do it?

Cheers!

[sunny_talwar]

My bad.... this should be the right expression

Avg({<[FINISH DATE] = {"$(='>=' & Date(MakeDate(2018, 1, 1), 'DD/MM/YYYY'))"}>} Aggr([FINISH DATE] - [START DATE], ID))