I am working on a Data Set. I need to ignore the Weekends and USA Public Holidays. I need to build a Master calendar for these dates . For example if you look at the chart on Monday it will show previous day data that is Friday (Not Sat or Sun) or ignore any Holidays.
Using NetWorkDays() while generating MasterCalendar you can flag each date whether or not it is a holiday and then use this flag in, say, set analysis.
Hi, unfortunatelly I am not able to attach qvf for some reason, so here's the script I used (based on something I did for a client from the Czech republic, so contains Czech holidays, adjust to your needs):
Data:
LOAD
"date" as Date,
Stock_Price,
Stock_Name
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is all_stocks_5yr_Sample);
// Master Calendar ---------------------------------------------------
[Calendar]:
LOAD
FieldValue('Date', RowNo()) as Date
AutoGenerate FieldValueCount('Date');
Left Join (Calendar) LOAD
Floor(Date) as %Date,
Date,
Day(Date) as Day,
WeekName(Date) as Week,
WeekDay(Date) as [Week Day],
MonthName(Date) as [YearMonth],
Month(Date) as Month,
Year(Date) as Year
RESIDENT Calendar;
// Holidays (customize this list to your locale)
LET NewYrD = '';
LET EasterFR = '';
LET EasterMO = '';
LET MayD = '';
LET VictD = '';
LET StCMD = '';
LET HusD = '';
LET StateD = '';
LET Indep = '';
LET FreedD = '';
LET XmasEv = '';
LET XmasD = '';
LET BoxingD = '';
FOR Y = Year('$(vStart)') to Year('$(vEnd)')
LET NewYrD = NewYrD & ',' & chr(39) & date(makedate($(Y), 1, 1), '$(DateFormat)') & chr(39); // New Year's Day
LET EasterFR= EasterFR & ',' & chr(39) & date(round(num(date(makedate($(Y), 4, day(minute($(Y) / 38) / 2 + 55)), '$(DateFormat)')) / 7) * 7 - 6-2) & chr(39); // Good Friday
LET EasterMO= EasterMO & ',' & chr(39) & date(round(num(date(makedate($(Y), 4, day(minute($(Y) / 38) / 2 + 55)), '$(DateFormat)')) / 7) * 7 - 6+1) & chr(39); // Easter Monday
LET MayD = MayD & ',' & chr(39) & date(makedate($(Y), 5, 1), '$(DateFormat)') & chr(39); // May Day
LET VictD = VictD & ',' & chr(39) & date(makedate($(Y), 5, 8), '$(DateFormat)') & chr(39); // Victory Day
LET StCMD = StCMD & ',' & chr(39) & date(makedate($(Y), 7, 5), '$(DateFormat)') & chr(39); // St. Cyril and St. Methodius Day
LET HusD = HusD & ',' & chr(39) & date(makedate($(Y), 7, 6), '$(DateFormat)') & chr(39); // Jan Hus Day
LET StateD = StateD & ',' & chr(39) & date(makedate($(Y), 9, 28), '$(DateFormat)') & chr(39); // Czech Statehood Day (St. Wencelsas Day)
LET Indep = Indep & ',' & chr(39) & date(makedate($(Y), 10, 28), '$(DateFormat)') & chr(39); // Czechoslovak Republic Independence Day
LET FreedD = FreedD & ',' & chr(39) & date(makedate($(Y), 11, 17), '$(DateFormat)') & chr(39); // Freedom and Democracy Day
LET XmasEv = XmasEv & ',' & chr(39) & date(makedate($(Y), 12, 24), '$(DateFormat)') & chr(39); // Christmas Eve
LET XmasD = XmasD & ',' & chr(39) & date(makedate($(Y), 12, 25), '$(DateFormat)') & chr(39); // Christmas Day
LET BoxingD = BoxingD & ',' & chr(39) & date(makedate($(Y), 12, 26), '$(DateFormat)') & chr(39); // Boxing Day
NEXT Y
// all holidays into one variable for use in networkdays (start_date, end_date [, vHolidays])
LET vHolidays = mid(NewYrD & EasterFR & EasterMO & MayD & VictD & StCMD & HusD & StateD & Indep & FreedD & XmasEv & XmasD & BoxingD, 2);
// Attach holiday flag and name
LEFT JOIN (Calendar) LOAD DISTINCT
%Date,
NetWorkDays(MonthStart(Date), MonthEnd(Date), $(vHolidays)) as [Working Days in Month],
NetWorkDays(MonthStart(Date), Date, $(vHolidays)) as [#Working Day in Month],
NetWorkDays(YearStart(Date), Date, $(vHolidays)) as [#Working Day in Year],
If(Match(Date(%Date, '$(DateFormat)') $(NewYrD)), 'New Year''s Day',
if(match(date(%Date, '$(DateFormat)') $(EasterFR)), 'Good Friday',
if(match(date(%Date, '$(DateFormat)') $(EasterMO)), 'Easter Monday',
if(match(date(%Date, '$(DateFormat)') $(MayD)), 'May Day',
if(match(date(%Date, '$(DateFormat)') $(VictD)), 'Victory Day',
if(match(date(%Date, '$(DateFormat)') $(StCMD)), 'St. Cyril and St. Methodius Day',
if(match(date(%Date, '$(DateFormat)') $(HusD)), 'Jan Hus Day',
if(match(date(%Date, '$(DateFormat)') $(StateD)), 'Czech Statehood Day (St. Wencelsas Day)',
if(match(date(%Date, '$(DateFormat)') $(Indep)), 'Czechoslovak Republic Independence Day',
if(match(date(%Date, '$(DateFormat)') $(FreedD)), 'Freedom and Democracy Day',
if(match(date(%Date, '$(DateFormat)') $(XmasEv)), 'Christmas Eve',
if(match(date(%Date, '$(DateFormat)') $(XmasD)), 'Christmas Day',
if(match(date(%Date, '$(DateFormat)') $(BoxingD)), 'Boxing Day',
if(Num([Week Day])>= '5', 'Weekend', 'Working day'
)))))))))))))) as Holidays,
If(Match(Date(%Date, '$(DateFormat)'), $(vHolidays)), Dual('Yes', 1), Dual('No',0)) as @IsHoliday,
If(Match(Date(%Date, '$(DateFormat)'), $(vHolidays)) or Num([Week Day])>= '5', Dual('No',0), Dual('Yes', 1)) as @IsWorkingDay
RESIDENT Calendar;
LET Y = null();
LET NewYrD = null();
LET EasterFR = null();
LET EasterMO = null();
LET MayD = null();
LET VictD = null();
LET StCMD = null();
LET HusD = null();
LET StateD = null();
LET Indep = null();
LET FreedD = null();
LET XmasEv = null();
LET XmasD = null();
LET BoxingD = null();
// End of Holidays section ----------------------------------------------
