Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sarfaraz_sheikh
Contributor III
Contributor III

How to calculate Network Hours between two dates

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

Sarfaraz

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MK_QSL
MVP
MVP

Calculate hours between two Date/Time strings

This is what you are looking for...!

sarfaraz_sheikh
Contributor III
Contributor III
Author

Dear Jonathan,

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

Sarfaraz

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Keitaru
Creator
Creator

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