Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Week patterns

Hi everyone,

I have seen several excelent tutorials made by the community dealing with calculating durations in working hours.

  • I've seen the solution of using networkdays() including holidays
  • some using interval(sum(end-start) where weekday(date<5) that offer including saturdays in the calculation.

Today i'd like to go a little bit further using week patterns which mean that each day can have different durations :

ServiceITCartography:

LOAD * INLINE [

Service,          WeekDay,    Start,          End

ServiceIT 1,         Mon.,          08:00          20:00

ServiceIT 1,         Tues.,         08:00          20:00

ServiceIT 1,         Wed.,         08:00          15:00

ServiceIT 1,         Thur.,         08:00          20:00

ServiceIT 1,         Fri.,           08:00          20:00

ServiceIT 1,         Sat.,          09:00          16:00

ServiceIT 1,         Sun.,         00:00          00:00

];

Incidents:

LOAD * INLINE [

IncidentID, Service, IncidentStart,         IncidentEnd

Incident1,  ServiceIT 1, 01/01/2013 08:12:21, 08/01/2013 21:47:02

];

Is there any way of applying this week pattern to calculate incidents durations ? 

There might be a solution using a case if on weekday but this sounds slow & dirty...

Thanks in advance !

3 Replies
Highlighted
Not applicable

Re: Week patterns

Anyone has ever faced this issue ?

Highlighted
Partner
Partner

Re: Week patterns

I don't know if I understand your question correctly but do you want to count the number of incidents per day?

Highlighted
Not applicable

Re: Week patterns

Hi,

I have as an input incidents (intervals). Those incidents can happen at any time.

I want to measure the real impact of those incidents on offices that have various opening and closing time.

This means that if the incident happen during the night, the office is closed and the incident have no impact on business. On the other hand, if an incident starts at 7AM and ends at 1PM, if the office opens at 9AM, the incident impact for the business is 4:00.

I'm today able to calculate this impact if every day of a standard week have the same opening and closing time by using :

temp5j_8h_20h:

LOAD

          subIntervalUniqueID,

          daystart(SubIntervalBegin)+iterno()-1 as Date,

          if(iterno()=1, rangemin(rangemax(frac(SubIntervalBegin),maketime($(vOuverture_Bureaux))),maketime($(vFermeture_Bureaux))), maketime($(vOuverture_Bureaux))) as Start,

          if(daystart(SubIntervalBegin)+iterno()-1=daystart(SubIntervalEnd+ $(#vEpsilon)), rangemax(maketime($(vOuverture_Bureaux)),rangemin(frac(SubIntervalEnd+ $(#vEpsilon)),maketime($(vFermeture_Bureaux)))),Maketime($(vFermeture_Bureaux))) as End

Resident SubIntervals

while daystart(SubIntervalEnd+ $(#vEpsilon)) >= daystart(SubIntervalBegin)+iterno()-1;

left join (SubIntervals)

LOAD

          subIntervalUniqueID,

          interval(sum(End-Start)) as Durée_ouvrée_incident_5j_8h_20h

          Resident temp5j_8h_20h

WHERE WeekDay(Date)<5 and not match(Date,$(vJours_Fériers))

GROUP BY subIntervalUniqueID;

DROP TABLE temp5j_8h_20h;

NB :

vOuverture_Bureaux = office open hour

vFermeture_Bureaux = office closing hour

My need today is to extend this code to generate durations based on various opening days.

To illustrate that, this is my standard week :

27-06-2013 12-30-34.png

and this is the impact of a incident starting at 06:00 a monday and ending at 19:00 a wednesday :

27-06-2013 12-31-18.png

In this example the business was impacted 31 hours for a 61 hours incident.

Any ideas ?

Thanks in advance