Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

Calculation of 72 hours using Date&Time

Hi Experts,

In file i  have four columns like Event start date,Event End date,Event start time,Event status time.

Here i want  to exclude the Weekends&Public holidays (Date and time)and i want to calculate the difference between the working days .

Event state date- Event end date & Event start time-Event status time.



for example:-

Event state date   - 7/4/2017(July)

Event Status Date - 8/16/2017(Aug)

Event start time- 7/4/2017 6:00:00 AM

Event status time - 8/16/2017 10:09:20 AM


Three Scenarios:

1. If Event start Date is an holiday (7/4/2017 is an holiday)

Expression:

Event start time-Event status time = 7/4/2017 6:00:00 AM -  8/16/2017 10:09:20 AM


Actual Output :

Working Hrs = 726 Hrs


Expected Output:

Working Hrs = 730 Hrs



2. If Event start date and Status date are working days


Expression:

Event start time-Event status time = 1/3/2017 1:00:00 PM -  2/23/2017 4:38:06 PM


Actual Output :

Working Hrs = 877 Hrs


Expected Output:

Working Hrs = 867 Hrs


3. If Event End Date is an holiday, exclude Event Status Date & Event Status time ( However there is no such values in the data currently but in future we might get it. Hence need this logic as well)




Kindly help to get the above three scenarios.

mtoloveisfailavinashelitestevedarkQlikView App Development‌@

38 Replies
Anonymous
Not applicable

i guess , you should start with Networkdays() function.

qlikview979
Specialist
Specialist
Author

Yes i am using what you suggested,but  i am not getting correct output.

Can you help me on this?

Regards

Mahesh

sunny_talwar

Can you explain why should Event start time-Event status time = 7/4/2017 6:00:00 AM -  8/16/2017 10:09:20 AM be 730 hrs?

YoussefBelloum
Champion
Champion

I was calculating this also, i didn't find that number...

qlikview979
Specialist
Specialist
Author

For example

the Person  have to speak on stage on  8/16/2017 for that he has  trained on 7/4/2017 6:00:00.A.M.  but unfortunately he has given the update on 8/16/2017  10:09:20 like (not able to attend the meeting ) .



so here we need to exclude Weekends&Public holidays and who are given the update before 72 hours.



regards

mahesh





qlikview979
Specialist
Specialist
Author

days we need to change hours

sunny_talwar

That is fine, but why is the number of hours 730 and not 726 the way you are already getting it?

qlikview979
Specialist
Specialist
Author

Hi bro,

I have attached my files please look at that  ,we doesn't have any calendar .we have date&time columns and holidays list.

qlikview979
Specialist
Specialist
Author

Hi Bro,

7/4/2017 6:00:00 AM -  8/16/2017 10:09:20 AM

the difference between these days is 31 days so here (31*24=744(744-14=730) hours  and start date  is holiday so need to consider from next day   and status date we are considering 24 hours but need to consider till 10 AM


regards

mahesh