Removing Bank holidays from line graph

[GeorgeEard]

Hi,

I have been able to remove weekends from my data using the formula:

=if([Creation Date] > '01.06.2024' and not match(weekday([Creation Date]),'Sa.','So.'),[Creation Date],null())

However, I still have bank holidays in the data that need to be removed as weekends have, how would I do this?

[PrashantSangle]

create flag for Holiday & Weekends in backends script then use that flag field to exclude it in set analysis.

Regards,

Prashant Sangle

[GeorgeEard]

Thanks Prashant, would you be able to explain how to do that?

[Qrishna]

do you have list of holidays?

[GeorgeEard]

1st January

29th March

31st March

1st April

6th May

27th May

26th August

25th December

26th December

[PrashantSangle]

if you created master calendar then join holiday table to that calendar and top of that using weekday() you will get day and on that basis using if else statement create flag for holiday & working_day.

Regards,

Prashant Sangle

[Qrishna]

try below:

LET vStartDate = Num(MakeDate(2024, 1, 1));
LET vEndDate = Num(MakeDate(2024, 12, 31));

Calendar:
LOAD *,
if(wildmatch(weekday(Date), '*Sa*','*Su*'), 1) as weekend_flag,
weekday(Date) as weekday
//,ApplyMap('map_bank_holidays', Num(Date), 0) as map_bank_holidays
;
LOAD
Date($(vStartDate) + IterNo() - 1) AS Date,
Year(Date($(vStartDate) + IterNo() - 1)) AS Year,
Month(Date($(vStartDate) + IterNo() - 1)) AS Month
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() - 1 <= $(vEndDate);

left join(Calendar)
map_bank_holidays:
//mapping
load 1 as bank_holidays_flag,
Num(Date(Date#(purgechar(subfield(BankHolidays, ' ', 1), 'stndrdth') & '/' &
match(Left(subfield(BankHolidays, ' ', 2), 3), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') & '/' & '2024',
'D/M/YYYY'), 'M/D/YYYY')) AS Date
inline [
BankHolidays
1st January
29th March
31st March
1st April
6th May
27th May
26th August
25th December
26th December
];

NoConcatenate
Cal_Less_Hols:
Load *
Resident Calendar
where not (bank_holidays_flag = 1 or weekend_flag = 1);
drop table Calendar;