Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 )
Thank you sir
Did perhaps you pick anything wrong in my understanding of what the code is doing in my original post?
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