Vacation Calendar

[ddd111rgg23]

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 

[Daniel_Castella · Accepted solution]

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

[Vegar · Accepted solution]

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/D...

[ddd111rgg23]

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,