topic Re: How to calculate Network Hours between two dates in QlikView

How to calculate Network Hours between two dates

sarfaraz_sheikh — Sun, 06 Mar 2016 10:47:48 GMT

Dear All,

I have the below data

Input:

LOAD * Inline

[

InteractionID, CreatedDate , ResolvedDate

2065789, 03/20/201601:22:15 PM , 03/23/201609:15:07 AM

2065790, 03/15/2016 10:40:10 AM , 03/15/2016 10:15:04 PM

2065791, 03/18/2016 03:20:20 PM , 03/22/2016 11:10:10 AM

];

i want to calculate the network hours between CreatedDate and ResolvedDate ...

I am familier with NetworkDays() of Qlikview and it is giving me the result according to days but i want it on hourly basis..

Is this possible if yes then please help me

Thanks

Sarfaraz

Re: How to calculate Network Hours between two dates

jonathandienst — Sun, 06 Mar 2016 10:57:44 GMT

Sarfaraz

As you have observed, there is a net work-days function, but no such function for hours. You will need to provide more information:

How many hours are worked per day?
If less than 24, what are the start and end times of each day?
If the start or end time is ever outside the workday window, should the hours be counted?
EDIT - is the work week Monday - Friday?

In simple teams, I would use NetWorkDays() to caluclate the number of full days, multiplies by the number of hours per workday. Then calculate and add the number of hours on the first day and the last. You will be using RangeMax and RangeMin to do this. Also it is useful to know that Qlikview dates are (like Excel) numbers where the integer part represents the date and the fractional part the time of day, so each hour is an interval of value 1/24. Finally you will need an interval function to return the number of hours (this changes the way it is displayed), or multiply the value by 24 to convert the value and return the number of hours.

Hope this helps you get started.

Re: How to calculate Network Hours between two dates

MK_QSL — Sun, 06 Mar 2016 11:06:36 GMT

Calculate hours between two Date/Time strings

This is what you are looking for...!

Re: How to calculate Network Hours between two dates

sarfaraz_sheikh — Sun, 06 Mar 2016 11:06:51 GMT

Dear Jonathan,

Can you please keep any practical example here for my understanding .....so that would be more helpful for me

Sarfaraz

Re: How to calculate Network Hours between two dates

jonathandienst — Sun, 06 Mar 2016 11:09:25 GMT

Unfortunately I don't have any current examples for you. I last did it for a vehicle finance client some year ago. But if you search the forum or follow the link provided by Manish, you might find practical examples. I can also provide more detailed help if you respond to my questions.

Re: How to calculate Network Hours between two dates

Keitaru — Fri, 01 Feb 2019 12:26:34 GMT

Having the same issue however I do also want to include Weekends into the calculation