Exclude Weekends and US Holidays from Report

[azmeerrehan]

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.

Attached please find the sample data set.  

Please attach a QVF for solution if possible

[skamath1]

You can use the function networkdays to compute. 

Below is the link for more information and examples

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[azmeerrehan]

BUt I am looking to built some sort of a MAster Calendar for Weekends and Holidays

[juraj_misina]

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.

[azmeerrehan]

Here is a sample data set can you please generate the master calendar for me as  you mentioned.  

Please use Qlik Sense if you can so that I can open it on my Side

[juraj_misina]

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 ----------------------------------------------