Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
| |
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:
Can someone help me?
May be this
Avg({<[FINISH DATE] = {'*'}>} Aggr([FINISH DATE] - [START DATE], ID))
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
May be this
Avg({<[FINISH DATE] = {"$(=Date(MakeDate(2018, 1, 1), 'DD/MM/YYYY'))"}>} Aggr([FINISH DATE] - [START DATE], ID))
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!
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))