topic Calculate average lead time in QlikView

Calculate average lead time

Anonymous — Thu, 09 Nov 2017 12:26:05 GMT

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

START DATE

FINISH DATE

30/10/2017

06/11/2017

01/11/2017

08/11/2017

01/11/2017

08/11/2017

03/11/2017

07/11/2017

03/11/2017

05/11/2017

10/11/2017

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?

Re: Calculate average lead time

sunny_talwar — Thu, 09 Nov 2017 12:31:59 GMT

May be this

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

Re: Calculate average lead time

Anonymous — Thu, 09 Nov 2017 14:48:19 GMT

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

sunny_talwar — Thu, 09 Nov 2017 14:53:32 GMT

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

Anonymous — Thu, 09 Nov 2017 15:05:45 GMT

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

sunny_talwar — Thu, 09 Nov 2017 15:08:46 GMT

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