Calculation of 72 hours using Date&Time

[qlikview979]

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‌@

[qlikview979]

Hi Bro,

Please find the attached updated file.

here my  start date is on 23.01.2017  and status date 25.08.2017

when i select this dates my condition is showing "0" days difference.

here my condition is

(NetWorkDays([Event Status Date],[Event Start Date],$(vholidaylist))*24) as WorkingHrs

Regards

Mahesh

[qlikview979]

if i use condition like  (([Event Status Date]-[Event Start Date])) as WorkingHrs

its showing exact difference between this two dates(214)days include holidays and weekends

[qlikview979]

here my formula is([Event Start Date]-[Event Status Date])

not ([Event Status Date]-[Event Start Date])

[Anil_Babu_Samineni]

May be I should've open the QVW.. Let's see

[sunny_talwar]

NetWorkDays can only show positive difference between two dates... but I am not sure what the issue is... may be all you need is to multiply your output with -1?

In addition, you mentioned

when i select start date on  5/18/2017 and status date on 6/23/2017

What do you mean when you select? The calculation you asked for above is done in the script and not on the front end... selections are not going to change the result... I am not even sure what you are looking to get

[qlikview979]

Hi,

Please find the updated QVW and Excel file attached. Please help me to get the events(Event Id)  which are cancelled after 72hrs (i.e greater than 72hrs). Let me know if you need more information.

Please consider Event Start Date, Event Status Date, Event Start Time and Event Status Time.

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)

Regards

mahesh

[sunny_talwar]

Isn't this what we started with and then I thought you did say that it is working and you marked my response as correct... what happened? I want to help but unless I know what happened or changed I will be helpless

[qlikview979]

Sorry bro,

we are not getting correct result we need your help.

Regards

mahesh

[sunny_talwar]

Your new sample doesn't even seem to have 7/4/2017 Event Start Date