Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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;