Discussion Board for collaboration related to QlikView App Development.
Hi Qlikview Community,
I am trying to calculate the time difference in minutes between 2 time stamps?I would like to exclude weekends, holidays and consider work hours to be from 06:00AM to 11:00PM (Monday to Friday)?
How do i achieve this?
I have used the following expression to calculate the time difference:
and this gave me the following table:
However, it is correct only when the event time for started and finished assignment falls in the same day. However, i would like to exclude the weekends and including working hours for the day?
I am aware of network days. However, i am not able to write an expression to get what i need?
Can someone please help me with this?
I have attached qvw file for your reference.
@sunny_talwar @swuehl can you help me with this?
Go to Solution.
You can do it like shown inhttps://community.qlik.com/t5/QlikView-App-Development/Calculate-hours-between-two-Date-Time-strings...
This code uses input data that shows the start and end timestamps in the same record line.
You can transform your data that shows the timestamps in different lines using e.g. a Group by LOAD statement.
Hope this helps.
Thanks for the reply.
This post helps.
I did not want to load the data by grouping in to each line for each event. Because there are so many exceptions and inconsistencies. I have been able to capture that in the front end.
I have referenced the following expression described in the post. It worked perfectly fine for me.
- RangeMax(frac(Logdate), MakeTime(8)))
+ (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day
I have attached the file if anyone wants to use.