Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

segerchr
New Contributor II

Calculate Office Hours

Hey,

maybe you techs could help me by coding a KPI.

I have two dates. Createddate und Closeddate. Now i want to calculate the runtime.

Closeddate-Createdate ---> But only in office hours. The runtime should only be calculated between Mo-Fr / 06:00-23:00.

I do not have any idea, how to do this.

BR Christian

1 Solution

Accepted Solutions
andrey_krylov
Contributor III

Re: Calculate Office Hours

Try Stefan's expression, works fine

Re: Calculate hours between two Date/Time strings

Interval(

  Sum(rangesum(

  NetWorkDays(Createdate+1,Closeddate-1) * MakeTime(17)

  ,if(NetWorkDays(Closeddate,Closeddate),Rangemin(rangemax(frac(Closeddate),maketime(6)),maketime(23))-Rangemax(rangemin(frac(Closeddate),maketime(6)),maketime(6)),0)

  ,if(NetWorkDays(Createdate,Createdate),Rangemin(rangemax(frac(Createdate),maketime(23)),maketime(23))-Rangemax(rangemin(frac(Createdate),maketime(23)),maketime(6)),0)

  ,if(NetWorkDays(Createdate,Createdate) and floor(Createdate)=floor(Closeddate),-MakeTime(17))

  ))

,'hh:mm')

19 Replies

Re: Calculate Office Hours

NetWorkdays(Createddate , Closeddate) will give you the number of M-F days between the dates.  But you seem to imply there is also a time of day component?


-Rob

andrey_krylov
Contributor III

Re: Calculate Office Hours

Hi Christian,  as an idea if  the Createdate and Closeddate dates are  the same

Sum({< Createdate= {"=not Match(WeekDay(Createdate), 'Sun', 'Sat')"} >}

  Interval(

  RangeMin(Date(Closeddate, 'YYYY-MM-DD hh:mm'), Date#(Date(Closeddate, 'YYYY-MM-DD')&' 23:00', 'YYYY-MM-DD hh:mm'))

  -

  RangeMax(Date(Createdate, 'YYYY-MM-DD hh:mm'), Date#(Date(Createdate, 'YYYY-MM-DD')&' 06:00', 'YYYY-MM-DD hh:mm'))

  ,'hh:mm')

)

segerchr
New Contributor II

Re: Calculate Office Hours

Thanks for the code. I will try that tomorrow.

This script is for a chart, Not for the load Script to create a variable. Isnt it ?

BR christian

andrey_krylov
Contributor III

Re: Calculate Office Hours

You are right, it's for a chart

segerchr
New Contributor II

Re: Calculate Office Hours

Hey, i try to get the right data output, but at the moment it doesn`t work. Want do you mean with:

as an idea if  the Createdate and Closeddate dates are  the same ?

BR Christian

Re: Calculate Office Hours

I just played a bit with it and if your workingdays are only Mo-Fr you could use an approach like this one:

For these few records it fits but I assume there are other constellations which needs further adjustments but I think it shouldn't be too difficult to extend the logic.

Another possibility would be to create an isolated master-calendar with times (maybe the hours would be enough) and to flag there the workingdays (unless weekends and holidays) and the office-hours with 1 and also creating a record-id (with recno/rowno) which is mapped to your createddate and closedate and also served as a lookup-value for a set analysis and then you could use an expression like:

sum({< [id-calendar] = {">=[id-created]<=[id-closed]"}>} FLAG)

Here you will find a similar but a bit more complex scenario - maybe it's helpful in any way: Re: Timestamp / Zeitstempel : Berechnung Start ohne Pausen.

- Marcus

andrey_krylov
Contributor III

Re: Calculate Office Hours

I mean these events for each row should occur in one day. And what does it show? I tested yesterday and it worked.

segerchr
New Contributor II

Re: Calculate Office Hours

Hey, it could be that createdate is on monday and closedate on wednesday. With this constellation, the script is not working ? I try to create sample data to share. Thanks for helping @all.

andrey_krylov
Contributor III

Re: Calculate Office Hours

Ah, got it. This is much more interesting but maybe later

Community Browser