Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Master Calendar without weekends and hollidays

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

5 Replies
marcus_sommer
Not applicable

Re: Master Calendar without weekends and hollidays

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

Re: Master Calendar without weekends and hollidays

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

Re: Master Calendar without weekends and hollidays

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

Re: Master Calendar without weekends and hollidays

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

- Marcus

Not applicable

Re: Master Calendar without weekends and hollidays

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