Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calendar without weekends and hollidays

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;

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

5 Replies
marcus_sommer

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

Not applicable
Author

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.

Not applicable
Author

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;

marcus_sommer

For me it worked - are you sure that you always have weekends?

- Marcus

Not applicable
Author

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