Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead 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
swuehl
MVP
MVP

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;

View solution in original post

6 Replies
swuehl
MVP
MVP

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;

Not applicable
Author

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 ?

swuehl
MVP
MVP

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;

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 ?

swuehl
MVP
MVP

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;