Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
cluscombe
Not applicable

Fractional Workdays Between Two Time Stamps, Excluding Weekends

Working on a application that include lots of time stamps for how long a work activity stays in a certain state.

I need to figure out the number of fractional days elapsed between each time stamp taking the following into consideration, excluding weekend.

If I just wanted to know work days between two dates, networkdays function would work great.  But I also need to consider the hours.

For Example

Time In:     3/17/2017   9:00:00 AM

Time Out:   3/21/2017  12:01:00 PM

In the above example this task took the 17 (Friday), 20(Monday), and then 4 hours on the 21(tuesday, so I would expect a result of 2 days 4 hours (or 2.5 days as there are 8 hours in a work day).

I am stumped on this!

Thanks

Chris

5 Replies
swuehl
Not applicable

Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends

cluscombe
Not applicable

Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends

Hi Stephan -

Thanks for link.  Only part of I am confused about is how to get the output for duration in the right format.

For example, in the second record we have

8/18/12 09:20 AM,8/20/12 01:13 PM

I am getting a duration output of 5:13:00 AM.  Not sure what that is suppose to mean.

I suspect this has something to do with formating dates, but I can seem to figure it out.  I am looking for duration formatting that would look something like 1.5 to represent the 1.5 work days between DT1 and DT2.

I see you included TimestampFormat as a variable, but not sure if I should be using that to help with this.

Thanks again.

Chris

swuehl
Not applicable

Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends

Hi Chris,

the duration could be formatted using

=Interval( DurationFieldOrExpression, 'h:mm:ss')

to get rid of the AM / PM which is not correct for a duration.

But you are interested in showing the duration in fractions of a working day. 8 hours are represented as 1/3 of a day, so you need to divide the duration by 1/3:

=Num( DurationFieldOrExpression / (1/3), '0.0','.',',' )

DurationFieldOrExpression in both example lines is the field or expression as shown in the post I've linked to.

cluscombe
Not applicable

Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends

So something like this?

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *,

recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD

    ID,

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

    if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

    if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

    LOAD

    ID,

    num(interval(sum(End-Start)/1/3),'0.0','.',',' ) as Duration

Resident TMP

where WeekDay(Date)<5

and not match(Date,$(vHol))

group by ID;

drop table TMP;

Results for duration still seem wacky.  Attaching screen shot of straight table showing results.exmaple results.jpg

swuehl
Not applicable

Re: Fractional Workdays Between Two Time Stamps, Excluding Weekends

Almost. A small typo in the duration calculation (and it seems you are using a 10h working day?)

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *,

recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD

    ID,

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

    if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

    if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

    LOAD

    ID,

    interval(Sum(End-Start),'h:mm:ss') as DurationHours,

    num(sum(End-Start)/(10/24),'0.0','.',',' ) as Duration

Resident TMP

where WeekDay(Date)<5

and not match(Date,$(vHol))

group by ID;

drop table TMP;

ID DT1 DT2 Sum(Duration) Sum(DurationHours)
17,628333333321 176:17:00
28/18/12 09:20 AM8/20/12 01:13 PM0,521666666661625:13:00
38/17/12 09:20 AM8/20/12 01:13 PM1,388333333324413:53:00
48/17/12 09:20 AM8/19/12 01:13 PM0,866666666662798:40:00
56/27/12 12:41 PM7/6/12 4:38 PM7,395000000012873:57:00
66/29/12 4:45 PM7/6/12 4:19 PM4,956666666665149:34:00
78/1/12 09:00 AM8/3/12 10:00 AM2,099999999994221:00:00
88/3/12 03:00 PM8/6/12 09:00 AM0,44:00:00