Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
davidteke
Contributor II
Contributor II

NetworkDays Function

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              )





3 Replies
Kushal_Chawda

davidteke
Contributor II
Contributor II
Author

Thank you sir

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

beck_bakytbek
Master
Master

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