Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Not applicable

## Playing with dates, durations and intervals

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 160'10:0011:00
Incident 260'10:3011:30
Incident 330'16:0016: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,

1 Solution

Accepted Solutions
MVP

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

IncidentsTmp2:

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;

6 Replies
MVP

Maybe something along these lines:

SET TimeFormat = 'hh:mm';

IncidentsTmp:

CROSSTABLE (Type, Timestamp, 2)

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:

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:

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:

RESIDENT IncidentsTmp3;

Not applicable
Author

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 ?

MVP

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

IncidentsTmp2:

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;

Not applicable
Author

Wonderful !

Thaks alot

Not applicable
Author

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 ?

MVP

Similar issue like before:

IncidentsTmp3:

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

RESIDENT IncidentsTmp2 ORDER BY Timestamp desc;

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

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

IncidentsTmp:

CROSSTABLE (Type, Timestamp, 1)

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:

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:

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: