How to include a list of holidays directly from an excel file into NetworkDays or FWD() function

[pranaview]

Hi Guys,

I need to include a list of public holidays for multiple years while executing either NetworkDays() or Firstworkdate() or Lastworkdate() functions. If it was a small number of holidays then I could have just manually typed it inside the function but I don't want to do that now as the list is huge and will obviously change in the future as well. So, I thought of just adding the holiday dates in a column in excel and use it in the functions but I don't really know how to implement this in Qlik. 

Any help or suggestions on this will be great as I'm stuck here and need to get this sorted before I can move ahead.

Thanks!

Pranav

[tincholiver]

Hi Pranaview:

i know two ways:

first one, add in your master calendar :

NetWorkDays    (start_date,end_date, holiday_date1,holiday_dates,holiday_date3)   as work_days

another one by excel:

export the caledar and manually put 0 o 1 if the day is holiday or not.

For this you need to have working day in the calento too,

if(num(weekday(PeriodDate)=6) OR num(weekday(PeriodDate)=0),0,1) AS working_day

Load calendar from excel file.

its look like this:

[tincholiver]

I use this calendar for this, there you have the next 10 years.

Attached excel file that i´m use.

FOR i = 2018 TO 2028

LET curYear = $(i);
LET StartDate = makedate(curYear); //Devuelve el primer dia del Año
LET YearEnd = yearend(StartDate);
LET TotalDays = (YearEnd - StartDate) + 2; //Calcula el numero de dias del AÑO

TempCalendar: //Crea un calendario temporal
LOAD
date('$(StartDate)' + recno() - 1) AS PeriodDate
AUTOGENERATE(TotalDays - 1);


Next

Calendario:
Load
PeriodDate AS %FECHA,
PeriodDate AS FECHA,
Year(PeriodDate) AS AÑO,
num(month(PeriodDate)) AS MES_NRO,
DUAL(text(date(PeriodDate,'MMM')),num(month(PeriodDate))) AS MES,
DUAL(date(PeriodDate,'MMM') & ' ' & date(PeriodDate,'yyyy'),
Year(PeriodDate)*100+NUM(Month(PeriodDate))) AS MES_AÑO,
WeekDay(PeriodDate) AS DIA_SEM,
num(Week(PeriodDate),'00') AS SEMANA,
num(Day(PeriodDate),'00') AS DIA,
CEIL(Num(Month(PeriodDate))/3)&'°T' AS TRIM,
num(weekday(PeriodDate)) AS DIA_NRO,
if(num(weekday(PeriodDate)=6) OR num(weekday(PeriodDate)=0),0,1) AS DIA_HABIL
Resident TempCalendar;

STORE Calendario into C:\Nube\Wicham\Qapacitacion\Practicas\Calendario\CalendarioLargo.qvd(qvd);
//DROP TABLE Calendario;
DROP TABLE TempCalendar;