5 Replies Latest reply: Nov 9, 2017 10:08 AM by Sunny Talwar

# Calculate average lead time

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

IDSTART DATEFINISH DATE
130/10/201706/11/2017
201/11/201708/11/2017
201/11/2017
 08/11/2017
303/11/201707/11/2017
403/11/2017
505/11/201710/11/2017
505/11/201710/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?

• ###### Re: Calculate average lead time

May be this

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

• ###### Re: Calculate average lead time

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

• ###### Re: Calculate average lead time

May be this

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

• ###### Re: Calculate average lead time

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!

• ###### Re: Calculate average lead time

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))