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,

can you please help me on this

Need help in creating maps in Qliksense

Regards

mahesh

[sunny_talwar]

Have not used Qlik Sense much and have no idea about the maps within Qlik Sense

[qlikview979]

Thanks for  sharing information.

Can you please mark this question for  any other qlik  persons.

Regards

mahesh

[sunny_talwar]

Not sure who might be able to help. Sorry

[qlikview979]

ok bro,

Thanks.

[sunny_talwar]

No problem at all

[qlikview979]

Sunny Bro,in this thread we have updated files in this files there is no association  can u look on this.

Need help in creating maps in Qliksense

regards

mahesh

[qlikview979]

Hi Sunny Bro,

I need Help on the  when i select start date on  5/18/2017 and status date on 6/23/2017

the difference between this two dates is -27 days but in application what you have shared to me its showing +27 days

i want change that  and want to exclude saturday,sunday,public holidays also and Time fields calculation also

Can you please help me on this.its urgent requirement

regards

mahesh

[qlikview979]

can any one help me on this?

[Anil_Babu_Samineni]

As i stated, I don't have access to open Sunny's App. So, Will share few inputs once i reach the home.

To answer before one, If this fault regards Signs you could replace from one-next and next-one like. I changes field position only not the entire one. Vice versa you can do

(((NetWorkDays([Event Status Date],[Event Start 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,