How to calculate a period of time between two dates

[#1]

Hi everyone!

I didn't found a solution of my problem in the forums, so, I tried to post this issue here:

I need to calculate the time between n dates in rows (TimeLapsed column). I make this example in excel, but i need to resolve it in Qlikview to obtain aggr data by Area.

Example:

AreaWorkflow	ChangeDate	Status	TimeLapsed
MKT - Especialidades	06/05/2010 12:42	Started	NULL
MKT - Especialidades	06/05/2010 12:43	Area Revision	0,00
Impuestos	21/06/2010 22:37	Legal Revision	46,41
Asuntos Corporativos y Legales	19/07/2010 10:22	Aproved to Sign	27,49
MKT - Especialidades	19/07/2010 10:23	Sign off	0,00
MKT - Especialidades	19/07/2010 10:23	Aproved	0,00
Asuntos Corporativos y Legales	19/07/2010 16:39	Finish	0,26

Thanks in advance,

Sebastián.

[Miguel_Angel_Baeyens]

Hi Sebastian,

You may use inter record functions:

ChangeDate - Above(ChangeDate)

Is that what you mean?

[#3]

Yes, but I need to use an aggregation function if I'll use ChangeDate-Above(ChangeDate) this formula as expression return - (error).

Is correct in the theorical way, but Qlikview respond in negative form.

Thanks in advance,

Sebastián.

[Miguel_Angel_Baeyens]

Hello Sebastián,

This probably has to do with your date formatting. Something like (untested)

Interval(Date(ChangeDate) - Date(Above(ChangeDate)))

might work. If it doesn't create a new column just with

Above(ChagneDate)

just to check which value is your chart taking.

Hope that helps.

[#5]

Hi Miguel,

Thanks for your answer but when i try to use Above function, it returns - because the parameter must be an aggregation function.

Regards,

Sebastián.

[Oleg_Troyansky · Accepted solution]

I'd recommend calculating Time Lapsed in the load script and not in the chart expression. You need to use functions peek() and previous() to calculate the "current" and the "previous" values of the timestamp.

[johnw]

I agree with Oleg's recommendation. Something like this:

LOAD
SomeID
,AreaWorkflow
,Status
,ChangeDate
,if(SomeID=previous(SomeID),interval(ChangeDate-previous(ChangeDate))) as TimeLapsed
...

[#8]

John / Oleg,

Thanks in advance for your answer. It works ok and it improves the calculating time in the user document.

Good Weekend,

Sebastián.