topic Re: Vacation Calendar in Data Movement & Streaming

Vacation Calendar

ddd111rgg23 — Tue, 18 Nov 2025 07:49:33 GMT

hello

I'm working on data to represent the number of network days for hr , but i tried to apply vacation days to disclosed it from the calculation (WORKING_DAYS) but it is not working well< my script :

Holidays:
LOAD Date#(Holiday,'DD/MM/YYYY') as Holiday
INLINE [
Holiday
23/09/2024
23/02/2025
30/03/2025
31/03/2025
01/04/2025
02/04/2025
03/04/2025
06/06/2025
07/06/2025
08/06/2025
09/06/2025
10/06/2025
23/09/2025
];

//LET vHolidays = '23/09/2024,23/02/2025,30/03/2025,31/03/2025,01/04/2025,02/04/2025,03/04/2025,06/06/2025,07/06/2025,08/06/2025,09/06/2025,10/06/2025,23/09/2025';
LET vHolidays = '23/09/2024,23/02/2025,30/03/2025,31/03/2025,01/04/2025,02/04/2025,03/04/2025';
LET vWorkingDays = NetWorkDays(Date#('07/07/2024','DD/MM/YYYY'), Date#('22/04/2025','DD/MM/YYYY'), $(vHolidays));

MAP_LAST_OFFER:
MAPPING LOAD
Text(Trim(MRR_NO)) AS MRR_NO,
Date(LAST_OFFER_CREATION_DATE) AS LAST_OFFER_CREATIO
FROM [lib://HR/RR_DASHBOARD_DATA_V.qvd] (qvd);

NEW_EMPLOYEE_RECR_STATUS_QUERY1:
LOAD
MRR_NO,
DEPARTMENT,
SECTION,
CANDIDATE_STATUS,
IF(Text(Trim(MRR_NO)) = '119', Date#('07/07/2025','DD/MM/YYYY'),
IF(Text(Trim(MRR_NO)) = '118', Date#('27/03/2025','DD/MM/YYYY'),
Date(Floor(CREATION_DATE)))) AS CREATION_DATE_Override,
Date(Floor("TRUNC(PPA.LAST_UPDATE_DATE)")) AS "TRUNC(PPA.LAST_UPDATE_DATE)",
FULL_NAME,
Date(Floor(MRR_CREATION_DATE)) AS CREATION_DATE_1,
Year(MRR_CREATION_DATE) AS CreationYear,
Month(MRR_CREATION_DATE) as CreationMonth,
'Q' & Ceil(Month(MRR_CREATION_DATE)/3) AS CreationQuarter

FROM [lib://HR/NEW_EMPLOYEE_RECR_STATUS_QUERY.qvd] (qvd);

NEW_EMPLOYEE_RECR_STATUS_QUERY:
LOAD
*,
IF(
Text(Trim(MRR_NO)) = '119', Date#('19/06/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '1193', Date#('13/04/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '115', Date#('22/04/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '1191', Date#('14/07/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '118', Date#('28/05/2025','DD/MM/YYYY'),
Date(ApplyMap('MAP_LAST_OFFER', Text(Trim(MRR_NO)), Null())) ))))) AS LAST_OFFER_CREATIO,

NetWorkDays(
CREATION_DATE_Override,
IF(Text(Trim(MRR_NO)) = '119', Date#('19/06/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '1193', Date#('13/04/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '115', Date#('22/04/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '1191', Date#('14/07/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '118', Date#('28/05/2025','DD/MM/YYYY'),
Date(ApplyMap('MAP_LAST_OFFER', Text(Trim(MRR_NO)), Null()))
))))), $(vHolidays)) AS WORKING_DAYS

RESIDENT NEW_EMPLOYEE_RECR_STATUS_QUERY1
WHERE CreationYear = 2025 AND CANDIDATE_STATUS = 'Hired';

DROP TABLE NEW_EMPLOYEE_RECR_STATUS_QUERY1;

as now i did it manully for calculation (=Avg(
Aggr(RangeSum(WORKING_DAYS,
Alt(Pick(Match(Text(MRR_NO), '119','1193','115','122','1222','1218','1213','121238','11828','12096','12028', '12261','12257','12279'),
-11,-6,-6, -5,-5,-5,-5,-5,-5, -10,-11, 1,1,1 ), 0 )), MRR_NO )))

thank you

Re: Vacation Calendar

Daniel_Castella — Tue, 18 Nov 2025 10:54:08 GMT

Hi @ddd111rgg23

Try to put this in the first part of the script:

Holidays:
LOAD concat(chr(39)&Date#(Holiday,'DD/MM/YYYY')&chr(39),',') as Holiday
INLINE [
Holiday
23/09/2024
23/02/2025
30/03/2025
31/03/2025
01/04/2025
02/04/2025
03/04/2025
06/06/2025
07/06/2025
08/06/2025
09/06/2025
10/06/2025
23/09/2025
];

LET vHolidays = peek('Holiday',0,'Holidays');
LET vWorkingDays = NetWorkDays(Date#('07/07/2024','DD/MM/YYYY'), Date#('22/04/2025','DD/MM/YYYY'), $(vHolidays));

This should properly concatenate the values of the inline table.

Kind Regards

Daniel

Re: Vacation Calendar

Vegar — Tue, 18 Nov 2025 23:14:27 GMT

Hi @ddd111rgg23

I believe the issue you are facing is the lack of ' ' around each of your date values. The suggested solution from @Daniel_Castella is a way to solve that issue. Daniel's solution will give you the correct ' ' around your date values. Please verify that the date format used in that function is the same as your default date format, if not then you should format them accordingly before you create that variable.

An example of using Networkdays() with holiday parameters can be found here: https://help.qlik.com/en-US/qlikview/September2025/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/networkdays.htm

Re: Vacation Calendar

ddd111rgg23 — Sun, 23 Nov 2025 08:52:49 GMT

still the number showing not accurate,

the updated version :
Holidays:
//LOAD concat(chr(39)&Date#(Holiday,'DD/MM/YYYY')&chr(39),',') as Holiday
LOAD concat(chr(39)&Date#(Holiday,'DD/MM/YYYY')&chr(39),',') as Holiday

INLINE [
Holiday
23/09/2024
23/02/2025
30/03/2025
31/03/2025
01/04/2025
02/04/2025
03/04/2025
06/06/2025
07/06/2025
08/06/2025
09/06/2025
10/06/2025
23/09/2025
];

LET vHolidays = peek('Holiday',0,'Holidays');
//LET vWorkingDays = NetWorkDays(Date#('07/07/2024','DD/MM/YYYY'), Date#('22/04/2025','DD/MM/YYYY'), $(vHolidays));

and
NEW_EMPLOYEE_RECR_STATUS_QUERY:
LOAD
*,
IF(
Text(Trim(MRR_NO)) = '119968', Date#('19/06/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '119349', Date#('13/04/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '115954', Date#('22/04/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '119174', Date#('14/07/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '118289', Date#('28/05/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '122396', Date#('06/08/2025','DD/MM/YYYY'),
IF( Text(Trim(MRR_NO)) = '122548', Date#('08/07/2025','DD/MM/YYYY'),
Date(ApplyMap('MAP_LAST_OFFER', Text(Trim(MRR_NO)), Null())) ))))))) AS LAST_OFFER_CREATIO,

// NetWorkDays(
// CREATION_DATE_Override,
// IF(Text(Trim(MRR_NO)) = '119968', Date#('19/06/2025','DD/MM/YYYY'),
// IF( Text(Trim(MRR_NO)) = '119349', Date#('13/04/2025','DD/MM/YYYY'),
// IF( Text(Trim(MRR_NO)) = '115954', Date#('22/04/2025','DD/MM/YYYY'),
// IF( Text(Trim(MRR_NO)) = '119174', Date#('14/07/2025','DD/MM/YYYY'),
// IF( Text(Trim(MRR_NO)) = '118289', Date#('28/05/2025','DD/MM/YYYY'),
// Date(ApplyMap('MAP_LAST_OFFER', Text(Trim(MRR_NO)), Null()))
// ))))), $(vHolidays)) AS MRR_WORKING_DAYS

NetWorkDays(
CREATION_DATE_Override,
// LAST_OFFER_CREATIO,
$(vHolidays)) AS MRR_WORKING_DAYS

RESIDENT NEW_EMPLOYEE_RECR_STATUS_QUERY1
;

i used this in measure =NetWorkDays(CREATION_DATE_Override, LAST_OFFER_CREATIO, $(vHolidays))

when i use MRR_WORKING_DAYS there is no data showing,