topic Re: Calculation of 72 hours using Date&Time in QlikView

Calculation of 72 hours using Date&Time

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

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.

mto‌loveisfail‌avinashelite‌stevedark‌QlikView App Development‌@

Re: Calculation of 72 hours using Date&Time

Anonymous — Mon, 08 Jan 2018 15:20:40 GMT

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

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 15:28:31 GMT

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

Can you help me on this?

Regards

Mahesh

Re: Calculation of 72 hours using Date&Time

sunny_talwar — Mon, 08 Jan 2018 15:57:10 GMT

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?

Re: Calculation of 72 hours using Date&Time

YoussefBelloum — Mon, 08 Jan 2018 16:07:57 GMT

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

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:07:57 GMT

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

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:08:46 GMT

days we need to change hours

Re: Calculation of 72 hours using Date&Time

sunny_talwar — Mon, 08 Jan 2018 16:09:25 GMT

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

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:11:22 GMT

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.

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:27:47 GMT

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

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:31:37 GMT

i am excluding here 14 hours from status date it means need to consider from 8/16/2017 12:00:00 A M to

8/16/2017 10:00:00 AM (not full days that's why 24 hrs-10 hrs =10 hrs)

Regards

mahesh

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:33:02 GMT

sorry

24-10 =14 hours(excluding)

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:34:34 GMT

if you have any doubts please let me know.

Regards

Mahesh

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:40:45 GMT

please check my updated files

Re: Calculation of 72 hours using Date&Time

Anonymous — Mon, 08 Jan 2018 16:41:35 GMT

Re: Calculate hours between two Date/Time strings

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 16:44:06 GMT

please find my updated files

Re: Calculation of 72 hours using Date&Time

sunny_talwar — Mon, 08 Jan 2018 17:03:18 GMT

Try the attached and see if this works

Re: Calculation of 72 hours using Date&Time

qlikview979 — Mon, 08 Jan 2018 18:11:00 GMT

Hi Bro,

its not giving correct output

for example:-

Event start date:-5/18/2017 9:30:00 AM ( this start date we need consider only 9 AM to end of the day it means include (15 hours )) and Exclude first (9 hours)

Event Status Date:- 6/23/2017 1:50:04 PM(this date and we need consider till 1 PM it means 13 hours )))

and exclude remaining time(11 hours )

so this difference between this two dates is 27 days

here my calculation is 27*24=648

calculation=((648-9)-11)

result:-628

exclude 9 hours from start time

exclude 11 hours from status time

Re: Calculation of 72 hours using Date&Time

sunny_talwar — Mon, 08 Jan 2018 19:24:49 GMT

I think your status date if 6/23/2017 so in order to get the right hour time difference, you should be adding 13 hours instead of subtracting 11 hours. If you were finding difference between 6/24/2017 and 5/18/2017 then it would have been subtracting 11 hours. But if you don't agree then, try this

(((NetWorkDays([Event Start Date],[Event Status Date],$(vholidaylist))))*24)-(If(Exists(Nationalholidays, date(Floor([Event Start Date]))), 0, Hour([Event Start Time])))-(24-If(Exists(Nationalholidays, date(Floor([Event Status Date]))), 0, Hour([Event Status Time]))) as WorkingHrs,

Re: Calculation of 72 hours using Date&Time

qlikview979 — Tue, 09 Jan 2018 12:04:17 GMT

Hi Bro,

Awesome ,exactly its working fine.

Regards

Mahesh