Holiday in calendar

sara12 · ‎2023-04-17

Hello, I would like your help. I'm working on my calendar to make it exclude holidays and shift the dates based on vacation days, so that, for example, it shows the last 30 days. If it finds a week of vacation, it should shift back one week before that vacation to show, for instance, the last 7 days."

Here is my calendar script before modifications:

SET vFactTable = [Fact_flux]; // Fact table name with Date field that contains the dates in the master calendar
SET vFiscal = 1; // If you want fiscal calendar fields loaded, toggle to 1
SET vFiscalStartMonth = 7; // If you've selected to have fiscal calendar fields loaded, enter the start month of your fiscal year
SET vDateField1 = [date]; // Date field that contains earliest date in master calendar
SET vDateField2 = [];

if len('$(vDateField2)')=0 or '$(vDateField2)'='$(vDateField1)' then
let vDateField2 = '$(vDateField1)';
set vIntervalMatch = 0;
else
set vIntervalMatch = 1;
end if

Temp:
Load
min($(vDateField1)) as minDate,
max($(vDateField2)) as maxDate
Resident $(vFactTable);

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

IF num(Today())<$(varMaxDate) then
let varMaxDate = num(today());
End IF

IF NOT $(vFiscal) THEN

MasterCalendar:
LOAD *,
// YEAR
InYear(Date,$(varMaxDate),0)*-1 AS _InCurrentYear,
InYear(Date,$(varMaxDate),-1)*-1 AS _InLastYear,
InYearToDate(Date,$(varMaxDate),0)*-1 AS _InCurrentYTD,
InYearToDate(Date,$(varMaxDate),-1)*-1 AS _InLastYTD,
If(DayNumberOfYear(Date)<=DayNumberOfYear($(varMaxDate)),1,0) AS _InAnyYTD,

// QUARTER
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,
Ceil(Month/3) AS _QuarterNum,
InQuarterToDate(Date,$(varMaxDate),0)*-1 AS _InCurrentQTD,
InQuarterToDate(Date,$(varMaxDate),-4)*-1 AS _InLastYearQTD,
InQuarter(Date,$(varMaxDate),0)*-1 AS _InCurrentQuarter,
InQuarter(Date,$(varMaxDate),4)*-1 AS _InLastYearCurrentQuarter,
If(InQuarter(Date,$(varMaxDate),-1) AND DayNumberOfQuarter(Date)<=DayNumberOfQuarter($(varMaxDate)),1,0) AS _InPriorQTD,
InQuarter(Date,$(varMaxDate),-1)*-1 AS _InPriorQuarter,

// MONTH
Num(Month) AS _MonthNum,
If(InMonth(Date,$(varMaxDate),0) AND Date <= Date($(varMaxDate)),1,0) AS _InCurrentMTD,
InMonth(Date,$(varMaxDate),0)*-1 AS _InCurrentMonth,
If(InMonthToDate(Date,$(varMaxDate),-1),1,0) AS _InPriorMTD,
If(InMonth(Date,$(varMaxDate),-1),1,0) AS _InPriorMonth,
If(InMonthtoDate(Date,$(varMaxDate),-12),1,0) AS _InLastYearMTD,
Dual(Year & '/' & Month,MonthStart(Date)) AS [YearMonth],
AutoNumber(Month & Year,'MonthCounter') AS MonthID,
AutoNumber(Ceil(Month/3) & Year,'QuarterCounter') AS QuarterID
;
Load
TempDate AS Date,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,

// WEEK
week(TempDate) As Week,
//applymap('HolidayTable', TempDate, 'normal') as [flag jours travail],

// ROLLING MONTHS
If( TempDate > addmonths($(varMaxDate),-12) and TempDate <= $(varMaxDate),1) as _Rolling12,
If( TempDate > addmonths($(varMaxDate),-2) and TempDate <= $(varMaxDate),1) as _Rolling2,
If( TempDate > addmonths($(varMaxDate),-3) and TempDate <= $(varMaxDate),1) as _Rolling3,
If( TempDate > addmonths($(varMaxDate),-6) and TempDate <= $(varMaxDate),1) as _Rolling6,
If( TempDate > addmonths($(varMaxDate),-24) and TempDate <= addmonths($(varMaxDate),-12),1) as _Rolling13to24,
If(Date([TempDate]) >= Date(Today() - 30) and Date([TempDate]) < Today(),1) as [J-30], //Modélisation 30 derniers jours
If(Date([TempDate]) >= Date(Today() - 90) and Date([TempDate]) < Today(),1) as [J-90],
If(Date([TempDate]) = Date(Today() - 1) ,1) as [J-1],
If(Date([TempDate]) = Date(Today()) ,1) as [aujourd'hui]

Resident TempCalendar where weekday(TempDate) < 5;
DROP Table TempCalendar;

ELSE

LET vFiscalDayDifference = Num(MakeDate(Year($(varMaxDate)),$(vFiscalStartMonth),1))-Num(MakeDate(Year($(varMaxDate)),1,1))-1; //day difference from Normal Year and Fiscal Year

MasterCalendar:
LOAD *,
// YEAR
InYear(Date,$(varMaxDate),0)*-1 AS _InCurrentYear,
InYear(Date,$(varMaxDate),-1)*-1 AS _InLastYear,
InYearToDate(Date,$(varMaxDate),0)*-1 AS _InCurrentYTD,
InYearToDate(Date,$(varMaxDate),-1)*-1 AS _InLastYTD,
If(DayNumberOfYear(Date)<=DayNumberOfYear($(varMaxDate)),1,0) AS _InAnyYTD,
InYear(Date,$(varMaxDate),0,$(vFiscalStartMonth))*-1 AS _InCurrentFiscalYear,
InYear(Date,$(varMaxDate),-1,$(vFiscalStartMonth))*-1 AS _InLastFiscalYear,
InYearToDate(Date,$(varMaxDate),0,$(vFiscalStartMonth))*-1 AS _InCurrentFiscalYTD,
InYearToDate(Date,$(varMaxDate),-1,$(vFiscalStartMonth))*-1 AS _InLastFiscalYTD,
If(DayNumberOfYear(Date,$(vFiscalStartMonth))<=DayNumberOfYear($(varMaxDate),$(vFiscalStartMonth)),1,0) AS _InAnyFiscalYTD,

// QUARTER
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,
Ceil(Month/3) AS _QuarterNum,
InQuarterToDate(Date,$(varMaxDate),0)*-1 AS _InCurrentQTD,
InQuarterToDate(Date,$(varMaxDate),-4)*-1 AS _InLastYearQTD,
InQuarter(Date,$(varMaxDate),0)*-1 AS _InCurrentQuarter,
InQuarter(Date,$(varMaxDate),4)*-1 AS _InLastYearCurrentQuarter,
If(InQuarter(Date,$(varMaxDate),-1) AND DayNumberOfQuarter(Date)<=DayNumberOfQuarter($(varMaxDate)),1,0) AS _InPriorQTD,
InQuarter(Date,$(varMaxDate),-1)*-1 AS _InPriorQuarter,
Dual('Q' & Ceil([Fiscal Month]/3), Ceil([Fiscal Month]/3)) AS [Fiscal Quarter],
Ceil([Fiscal Month]/3) AS _FiscalQuarterNum,
InQuarterToDate(Date,$(varMaxDate),0,$(vFiscalStartMonth))*-1 AS _InCurrentFiscalQTD,
InQuarterToDate(Date,$(varMaxDate),-4,$(vFiscalStartMonth))*-1 AS _InLastYearFiscalQTD,
InQuarter(Date,$(varMaxDate),0,$(vFiscalStartMonth))*-1 AS _InCurrentFiscalQuarter,
InQuarter(Date,$(varMaxDate),-4,$(vFiscalStartMonth))*-1 AS _InLastYearCurrentFiscalQuarter,
InQuarter(Date,$(varMaxDate),-1,$(vFiscalStartMonth))*-1 AS _InPriorFiscalQuarter,
InQuarterToDate(Date,$(varMaxDate),-1,$(vFiscalStartMonth))*-1 AS _InPriorFiscalQTD,

// MONTH
Num(Month) AS _MonthNum,
If(InMonth(Date,$(varMaxDate),0) AND Date <= Date($(varMaxDate)),1,0) AS _InCurrentMTD,
InMonth(Date,$(varMaxDate),0)*-1 AS _InCurrentMonth,
If(InMonthToDate(Date,$(varMaxDate),-1),1,0) AS _InPriorMTD,
If(InMonth(Date,$(varMaxDate),-1),1,0) AS _InPriorMonth,
If(InMonthtoDate(Date,$(varMaxDate),-12),1,0) AS _InLastYearMTD,
Dual(Year & '/' & Month,MonthStart(Date)) AS [YearMonth],
AutoNumber(Month & Year,'MonthCounter') AS MonthID,
AutoNumber(Ceil(Month/3) & Year,'QuarterCounter') AS QuarterID,
Num([Fiscal Month]) AS _FiscalMonthNum,
Dual([Year] & '/' & [Fiscal Month],[Year] + (Mod(Month - $(vFiscalStartMonth), 12)+1)) AS [Fiscal YearMonth]
;
Load
TempDate AS Date,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
Dual(Month(TempDate),Mod(Month(TempDate) - $(vFiscalStartMonth), 12)+1) AS [Fiscal Month],
YearName(TempDate, 0, $(vFiscalStartMonth)) AS [Fiscal Year Long],
Year(AddMonths(TempDate,$(vFiscalStartMonth))) as [Fiscal Year],

// WEEK
week(TempDate) As Week,
Week(TempDate-$(vFiscalDayDifference)) AS [Fiscal Week],
//applymap('HolidayTable', TempDate, 'normal') as [flag jours travail],

// ROLLING MONTHS
If( TempDate > addmonths($(varMaxDate),-12) and TempDate <= $(varMaxDate),1) as _Rolling12,
If( TempDate > addmonths($(varMaxDate),-2) and TempDate <= $(varMaxDate),1) as _Rolling2,
If( TempDate > addmonths($(varMaxDate),-3) and TempDate <= $(varMaxDate),1) as _Rolling3,
If( TempDate > addmonths($(varMaxDate),-6) and TempDate <= $(varMaxDate),1) as _Rolling6,
If( TempDate > addmonths($(varMaxDate),-24) and TempDate <= addmonths($(varMaxDate),-12),1) as _Rolling13to24,
If(Date([TempDate]) >= Date(Today() - 30) and Date([TempDate]) < Today(), 1, 0) as [J-30],
If(Date([TempDate]) >= Date(Today() - 90) and Date([TempDate]) < Today(),1) as [J-90],
If(Date([TempDate]) = Date(Today() - 1) ,1) as [J-1],
If(Date([TempDate]) = Date(Today()) ,1) as [aujourd'hui]

Resident TempCalendar where weekday(TempDate) < 5;

DROP Table TempCalendar;

ENDIF

If $(vIntervalMatch) = 0 then
Rename table MasterCalendar to MasterCalendar_Temp;

Alias Date as $(vDateField1);
NoConcatenate
MasterCalendar:
Load * Resident MasterCalendar_Temp;

Drop table MasterCalendar_Temp;
ElseIf $(vIntervalMatch) = 1 then
Join IntervalMatch (Date)
Load $(vDateField1),$(vDateField2) Resident $(vFactTable);

join ($(vFactTable))
Load * Resident MasterCalendar;

Drop table MasterCalendar;
End If

marcus_sommer · ‎2023-04-18

I'm not sure if I understand your question right but I wouldn't exclude dates or skipping them by the calendar-creation else just querying the holidays with a mapping, like:

m: mapping load *, 1 as value inline [
date
x
y
];

Calendar:
load if(weekday(date) >= 5 or applymap('m', date, 0), 0, 1) as Flag, ...

Afterwards a sum(Flag) returned the working-days in regard to the selection. And you may also create descriptive flag-values like working-day, weekend, holiday ... It's a very simple method and you extend a lot of logic on it by an accumulation on a monthly/yearly level with interrecord-functions like peek() and previous() and/or aggregating the sums back and so on ...

sara12 · ‎2023-04-18

Thank you for your response, but the issue is that this method allows me to not take into account holidays, but it does not allow me to shift the time frame. For instance, if there was a holiday in the last 30 rolling days, when I select the last 30 days on my calendar, it only shows me 28 days. However, I would like to shift the calendar by one day to include the full 30 days.

I hope this clarifies my issue. Please let me know if you need more information.

marcus_sommer · ‎2023-04-18

This would be possible with the above suggestion - directly on the 0/1 Flag field by loading this field twice and the second time the dates with a zero-value in the flag are replaced by null() or by using it as condition for a list-box like: aggr(only({< Flag = {1}>} Date), Date) or with appropriate accumulations of these values within the calendar which results might be queried with something: ... FlagCum = {">=$(=max(FlagCum)-30)"}> ... or be used for any kind of clusters, like: ceil(FlagCum) / 30) or ... many possibilities ...

Set Analysis