topic Re: Playing with dates, durations and intervals in QlikView

Playing with dates, durations and intervals

Fri, 10 May 2013 11:44:27 GMT

Hi everyone,

I was wondering if someone would be able to help me with this durations problem.

I have as input several incidents :

	Duration	Start	End
Incident 1	60'	10:00	11:00
Incident 2	60'	10:30	11:30
Incident 3	30'	16:00	16:30

I'd like to find the duration of non-disponibility of my service but I cant just sum up durations because incidents can happen simultaneously.

Anyone's got a clue ?

Regards & Thanks,

Re: Playing with dates, durations and intervals

swuehl — Fri, 10 May 2013 12:32:33 GMT

Maybe something along these lines:

SET TimeFormat = 'hh:mm';

IncidentsTmp:

CROSSTABLE (Type, Timestamp, 2)

LOAD * INLINE [

Incident, Duration, Start, End

Incident 1, 60', 10:00, 11:00

Incident 2, 60', 10:30, 11:30

Incident 2b, 10', 10:10, 10:20

Incident 3, 30', 16:00, 16:30

];

IncidentsTmp2:

LOAD *,

if(Type='Start' and peek('Type') <> 'Start', Timestamp, peek(StartDT)) as StartDT,

if(Type='Start' , rangesum(peek('StartInc'),1), rangesum(peek('StartInc'),-1)) as StartInc

RESIDENT IncidentsTmp ORDER BY Timestamp;

DROP TABLE IncidentsTmp;

IncidentsTmp3:

LOAD * ,

if(Type='End' and peek('Type') <> 'End' and StartInc = 0, interval(Timestamp - StartDT)) as IntervalGroup

RESIDENT IncidentsTmp2 ORDER BY Timestamp desc;

DROP TABLE IncidentsTmp2;

Result:

LOAD interval(sum(IntervalGroup)) as OverallDuration

RESIDENT IncidentsTmp3;

Re: Playing with dates, durations and intervals

Fri, 10 May 2013 12:59:34 GMT

Thanks for this quick and helpful answer !

Your code is far from my level so I need to study it !

But one thing is bothering me : in this example, i should get as a output 2:00

from 10:00 to 11:00 caused by incident 1 (+60')

from 11:00 to 11:30 caused by the end of incident 2 (+30')

from 16:00 to 16:30 caused by incident 3 (+30')

Any ideas ?

Re: Playing with dates, durations and intervals

swuehl — Fri, 10 May 2013 13:12:28 GMT

You are right, there is an error in calculating StartDT in the first temporary table:

IncidentsTmp2:

LOAD *,

if(Type='Start' and rangesum(peek('StartInc')) = 0, Timestamp, peek(StartDT)) as StartDT,

if(Type='Start' , rangesum(peek('StartInc'),1), rangesum(peek('StartInc'),-1)) as StartInc

Resident IncidentsTmp order by Timestamp;

Re: Playing with dates, durations and intervals

Fri, 10 May 2013 13:34:03 GMT

Wonderful !

Thaks alot

Re: Playing with dates, durations and intervals

Tue, 14 May 2013 15:46:56 GMT

Hi,

I have noticed that if two incidents start or finish at the same time the cumulated value vanish.

Is there any ways to extend this example to :

-calculate only working hours intervals with workingdays

-each incident affects an application that may be used by several businesses. The aim is to have a view of cumulated indisponibility by businesses (i think that it may be possible by using group by even if we loose incidents details)

Any ideas ?

Re: Playing with dates, durations and intervals

swuehl — Tue, 14 May 2013 16:58:18 GMT

Similar issue like before:

IncidentsTmp3:

LOAD * ,

if(Type='End' and StartInc = 0, interval(Timestamp - StartDT)) as IntervalGroup

RESIDENT IncidentsTmp2 ORDER BY Timestamp desc;

If this doesn't help, please post your test incident time table as INLINE table load.

Regarding the interval calculation taking working hours into account, you can do it like this (check also :

http://community.qlik.com/message/272046#272046 )

SET TimestampFormat = 'M/D/YYYY hh:mm';

IncidentsTmp:

CROSSTABLE (Type, Timestamp, 1)

LOAD * INLINE [

Incident, Start, End

Incident 1, 1/1/2013 10:00, 1/2/2013 11:00

Incident 2, 1/2/2013 10:30, 1/2/2013 11:30

Incident 2b, 1/2/2013 10:10, 1/2/2013 10:20

Incident 3, 1/2/2013 16:00, 1/5/2013 16:30

Incident 4, 1/2/2013 15:40, 1/4/2013 16:00

incident x, 1/3/2013 15:40, 1/7/2013 16:30

];

IncidentsTmp2:

LOAD *,

if(Type='Start' and rangesum(peek('StartInc')) = 0, Timestamp, peek(StartDT)) as StartDT,

if(Type='Start' , rangesum(peek('StartInc'),1), rangesum(peek('StartInc'),-1)) as StartInc

Resident IncidentsTmp order by Timestamp;

DROP TABLE IncidentsTmp;

IncidentsTmp3:

LOAD * ,

if(Type='End' and StartInc = 0,

Interval(

rangesum( NetWorkDays(StartDT+1,Timestamp-1,$(vHol)) * MakeTime(8)

//8 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(Timestamp,Timestamp,$(vHol)),

Rangemin(rangemax(frac(Timestamp),maketime(15)),maketime(18))-Rangemax(rangemin(frac(Timestamp),maketime(15)),maketime(15))

+Rangemin(rangemax(frac(Timestamp),maketime(8)),maketime(13))-Rangemax(rangemin(frac(Timestamp),maketime(8)),maketime(8)),0)

// working hours last day

,if(NetWorkDays(StartDT,StartDT,$(vHol)),Rangemin(rangemax(frac(StartDT),maketime(18)),maketime(18))-Rangemax(rangemin(frac(StartDT),maketime(18)),maketime(15)) +Rangemin(rangemax(frac(StartDT),maketime(13)),maketime(13))-Rangemax(rangemin(frac(StartDT),maketime(13)),maketime(8)),0)

// working first day

, if(NetWorkDays(StartDT,StartDT,$(vHol)) and floor(StartDT)=floor(Timestamp),-MakeTime(8))

// correct for first equals last day

)

) as IntervalGroup

RESIDENT IncidentsTmp2 ORDER BY Timestamp desc;

DROP TABLE IncidentsTmp2;

Result:

LOAD interval(sum(IntervalGroup)) as OverallDuration

RESIDENT IncidentsTmp3;