topic NetworkDays Function in QlikView

NetworkDays Function

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

Hi,

I am new to qlikview and I am working on a logic of calculating the Turn Around Time between a date range [date/time] fields

I found code posted by Rob Wunderlich in the below thread.

Calculate hours between two Date/Time strings

Could you kindly assist by clarifying the below code lines. I have done some research on QlikView Help and here's my understanding of the below logic so far.

Line 1: The Interval() is used to return the results as time. Please correct me if I am wrong

Line 2: The RangeMin() is to limit the time to 17:00 hours. Please correct me if I am wrong

Line 3: The RangeMax() is to limit the time to 8:00 hours. Please correct me if I am wrong

Line 4: The NetWorkDays() is to return the number of days. The results is returned as time. Please correct me if I am wrong, why are we subtracting a 1 or a day???

1 =Interval(

2 (RangeMin(frac(Closedate), MakeTime(17)) -

3 RangeMax(frac(Logdate), MakeTime(8)))

4 + (NetWorkDays(Logdate, Closedate-1) * MakeTime(9)) // Only 9 hours per whole day

5 )

Re: NetworkDays Function

Kushal_Chawda — Wed, 24 May 2017 09:08:24 GMT

see this

Business Working Hours Calculation

Re: NetworkDays Function

Anonymous — Wed, 24 May 2017 09:41:11 GMT

Thank you sir

Did perhaps you pick anything wrong in my understanding of what the code is doing in my original post?

Re: NetworkDays Function

beck_bakytbek — Wed, 24 May 2017 09:56:12 GMT

Hi Teke,

here is is an example of NetWorkingDays:

step 1: type that in script:

Holiday:

load

Date(Date#(Datum, 'DD.MM.YYYY')) as Datum

Inline [

Datum,

01.05.2017

06.05.2017

07.05.2017

13.05.2017

14.05.2017

20.05.2017

21.05.2017

29.04.2017

30.04.2017

15.04.2017

13.04.2017

14.04.2017

17.04.2017

];

ConcatTemp:

Load

Concat(Chr(39) & Datum & Chr(39), ',') as HolidaysDay

Resident Holiday;

Let vMyHoliday = FieldValue('HolidaysDay',1);

Let vCurrentMonth = Month(Today());

TurnoverTab:

Load

Date(Date#(TurnoverDate, 'DD.MM.YYYY')) as TurnoverDate, Turnover

Inline [

TurnoverDate,Turnover

03.05.2017, 230

04.05.2017, 25

16.05.2017, 66

10.05.2017, 120

19.05.2017, 32

12.04.2017, 100

05.04.2017, 12

];

Sales:

Load *,

Month(TurnoverDate) as Month,

MonthName(TurnoverDate) as MonthYear,

if(Year(TurnoverDate) = Year(Today()) and Month(TurnoverDate) = Month(Today()),

NetWorkDays(MonthStart(Today()),(Today()),$(vMyHoliday)),NetWorkDays(MonthStart(TurnoverDate),MonthEnd(TurnoverDate),

$(vMyHoliday))) as WorkingDays

Resident TurnoverTab;

drop Table TurnoverTab, Holiday;

step 2: on UserInterface: on Chart:

create a straigh table:

Use as Dimension:MonthYear, WorkingDays,

and as Expression use: Sum(Turnover) / WorkingDays as Avg Sales

I hope that exaple helps you to understand this issue.

Beck