Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I need to be able to show Dates exluding weekends and hollidays. Is there a simple way to do it? Thanks in advance for your help.
Here is a script for my calender that I use.
/*************** MinMax Table *************
Keeps minimum and maximum Date value from Facts table
*/
MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT MainTable;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
/*************** Temporary Calendar *************
Generates a single table with one field containing
all existing dates between MinDate and MaxDate.
*/
TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
/*************** Master Calendar ***************
*/
MasterCalendar:
LOAD
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
Weekends could be removed per where weekday(TempDate) < 5. Holidays should be loaded in a mapping-table before you generated your calendar and then where applymap('HolidayTable', TempDate, 'not found') <> 'not found'.
where weekday(TempDate) < 5 and applymap('HolidayTable', TempDate, 'not found') <> 'not found'
- Marcus
Weekends could be removed per where weekday(TempDate) < 5. Holidays should be loaded in a mapping-table before you generated your calendar and then where applymap('HolidayTable', TempDate, 'not found') <> 'not found'.
where weekday(TempDate) < 5 and applymap('HolidayTable', TempDate, 'not found') <> 'not found'
- Marcus
Hi Roman,
From your script, where you have excluded weekends and holidays?
Try to use If condition to check the date is weekend or not and store the value in a filed called WeekendFlag (having 1 & 0).
Ex: If(Date = Weekend(Date),1,0) as WeekendFlag.
Try to use Lookup for checking holidays and use another flag.
Load the same table by eliminationg the corespoing flags WeekendFlag and HolidayFlag.
Hi Marcus,
maybe I am doing something wrong but I did add that WHERE clouse... though it is still loads me the weekends. I need to get rid of those when I use Date. and then lets say i choose year and then month.. but then I only need to se the working dates... withouth weeknds
here is my edited script with the where clouse
/*************** MinMax Table *************
Keeps minimum and maximum Date value from Facts table
*/
MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT MainTable;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
/*************** Temporary Calendar *************
Generates a single table with one field containing
all existing dates between MinDate and MaxDate.
*/
TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
/*************** Master Calendar ***************
*/
MasterCalendar:
LOAD
TempDate AS Date,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
Where Weekday(TempDate) < 5
ORDER BY TempDate ASC;
DROP TABLE TempCal;
For me it worked - are you sure that you always have weekends?
- Marcus
YES! actually it does work. Thank you! The reason why it was still showing me all the days was because I had (under the axes) Continious checked in the chart option!!!