Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Networks days function

Good day

Please assist with a network days function that will exclude weekends
and round off the days to 2 decimal places.

Eg. 6 hours = 0.25 days  or

36 hours = 1.50 days or

1 day 18 hours = 1.75 days

This is for Turn Around Time (TAT) calculations.

Regards

Charles

Tags (1)
8 Replies
balrajahlawat
Esteemed Contributor

Re: Networks days function

MVP
MVP

Re: Networks days function

Have a look for example at this suggested approaches:

Re: Calculate hours between two Date/Time strings

Adapt the start and end times as necessary (potentially even simplifying the expression when your start and end timestamps start and end with each day.

To round to 2 digits, just add a Round() function

Round(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)  

// 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

)

, .01)

Not applicable

Re: Networks days function

Hi

Can you please assist with a Round(Networkdays()) function to 2 decimal places where date includes time.

My script is  Round(NetWorkDays(PF_DateStepStarted,Today(),$(vPublicHolidays)),2)

but it's not working.

PF_DateStepStarted format: 2015-11-30 17:26:30.000

Thanks

balrajahlawat
Esteemed Contributor

Re: Networks days function

try this:

Round(NetWorkDays(PF_DateStepStarted,Today(),$(vPublicHolidays)),0.01)


Hope this will help

Not applicable

Re: Networks days function

Hi

It worked but it didn't convert the time to decimal.

i'm getting 2.00 , 1.00, 5.00

I think it needs a function to convert days to hours then say that eg. 30 hours is 1.25 days.

balrajahlawat
Esteemed Contributor

Re: Networks days function

also make sure format of all date fields should be common like:

let vPublicHolidays=Date(max(HolidayDate), 'DD/MMM/YYYY')      //Logic part can differ for same


Round(NetWorkDays(date(PF_DateStepStarted,'DD/MMM/YYYY'),date(Today(),'DD/MMM/YYYY/),$(vPublicHolidays)),0.01)


Hope it will be helpful...

balrajahlawat
Esteemed Contributor

Re: Networks days function

NetworkDays() will return number of days, now if you want to convert it into Time like into Hours etc.

try like:

NetWorkDays(date(PF_DateStepStarted,'DD/MMM/YYYY'),date(Today(),'DD/MMM/YYYY/),$(vPublicHolidays))*24             //will convert it into Hours and then use Round().

MVP
MVP

Re: Networks days function

NetworkDays() function will only return complete days, i.e. integers. So you won't be able to do a more granular calculation only using this function. You'll need to consider the time of your timestamps by your own, as I showed above.

Here is an adaption to 24 hour working days:

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

Let vHol = Num(Makedate(2015,12,25)); // just an example

LOAD

Round(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol))

// 24 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), frac(DT2) ,0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),1 - frac(DT1) ,0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2), -1) // correct for first equals last day

)

, .01) as Interval,

*

INLINE [

DT1, DT2

28/11/2015 4:00:00 PM, 01/12/2015 4:00:00 AM

27/11/2015 4:00:00 PM, 01/12/2015 4:00:00 AM

];

Community Browser