topic Re: Master Calendar without weekends and hollidays in QlikView

Master Calendar without weekends and hollidays

Fri, 24 Jan 2014 14:13:25 GMT

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;

Re: Master Calendar without weekends and hollidays

marcus_sommer — Fri, 24 Jan 2014 14:22:45 GMT

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

Re: Master Calendar without weekends and hollidays

Fri, 24 Jan 2014 14:25:21 GMT

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.

Re: Master Calendar without weekends and hollidays

Fri, 24 Jan 2014 14:45:27 GMT

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;

Re: Master Calendar without weekends and hollidays

marcus_sommer — Fri, 24 Jan 2014 16:15:49 GMT

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

- Marcus

Re: Master Calendar without weekends and hollidays

Fri, 24 Jan 2014 16:36:47 GMT

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