topic Time Intervals excluding weekends in QlikView

Time Intervals excluding weekends

Fri, 28 Jan 2011 17:37:43 GMT

Hello all,

I've searched high and low on the forums and haven't seen any similar questions asked.

What I need is to be able to measure the time interval between 2 timestamps, excluding weekends. So, the distance between friday at noon to sunday at noon would be 24 hours instead of 72.

I'm aware of the networkdays() function, but that is only precise down to the number of days and not the number of hours. Dividing it by 24 wouldnt give me the precision I'm looking for.

Anybody run across this?

Time Intervals excluding weekends

hector — Fri, 28 Jan 2011 18:34:09 GMT

i'm not sure if this will help you, but can be a first step to the solution

If we have (Feb 2nd 2011 - Jan 28th 2011) = 4 days

but, you can substract 2 days for every 7 days of difference, i mean this

(Feb 2nd 2011- Jan 28th 2011) - (ceil((Feb 2nd 2011- Jan 28th 2011)/7)*2) => 4 - (ceil(4/7) * 2) = > 4 - (1*2) => 2

so summing upl for every 7 days of difference, you need to substract 2 days (Sat and Sun)

Hope it helps

rgds

Time Intervals excluding weekends

Fri, 28 Jan 2011 18:51:41 GMT

Hi Hector,

Thanks for the reply. That's certainly a good thought but it leaves me short of my goal in a couple ways:

1. What if the date interval doesn't include a weekend? Friday at noon to Friday at 5PM, for example.

2. It still does not get me down to an hour level precision.

If only they had a networktimestamp() function that work similarly to networkdays() but gave the time interval excluding weekends rather than the days...

Regards,

Time Intervals excluding weekends

hector — Fri, 28 Jan 2011 19:17:59 GMT

Hi again

Answering your question


mg119888 wrote:
1. What if the date interval doesn't include a weekend? Friday at noon to Friday at 5PM, for example. <div></div>

So, you need to control the week number, with the week function, if they are different, includes 1 weekend


mg119888 wrote:
2. It still does not get me down to an hour level precision.

There are 2 options, interval(timestamp2 - timestamp1,'hh') will return the difference in hours

(timestamp2 - timestamp1) *24 will return the difference in hours but numeric

Any way, try this example and tell us

Regards,

Time Intervals excluding weekends

Fri, 28 Jan 2011 20:06:12 GMT

Brilliant! Thank you sir. Exactly what I was looking for.

Only one problem left - start and end dates CAN occur on weekends, so I need to somehow roll all weekend dates into Friday and/or Monday.

Time Intervals excluding weekends

hector — Fri, 28 Jan 2011 20:27:52 GMT

You are welcome

You can check what day is (Mon - Sun) with weekday(), this is a dual function, so has numbers from Mon =0 to Sun = 6,

so if your date is Sat (6), will be yourdate - 1 (Friday), and in the other case you can sum days to move from Sat to Mon

Rgds and good luck