topic Help creating Calendar that excludes weekends and holidays in QlikView

Help creating Calendar that excludes weekends and holidays

Fri, 02 Aug 2013 14:10:14 GMT

Hello,

I've run into a bit of trouble when trying to create a calendar that excludes holidays and weekends. I have this working code for a regular calendar:

LET vMaxDate = num(today());

LET vMinDate = num(Date#('25/02/2008', 'DD/MM/YYYY'));

//Build the calendar with most date dimensions

LET vToday=today(1);

Calendar:

LOAD

Date($(vMinDate)+Iterno()-1) as InvoiceDate

,Date($(vMinDate)+Iterno()-1) as DateDual

,Day($(vMinDate)+Iterno()-1) as Day

,week($(vMinDate)+Iterno()-1) as Week

,Month($(vMinDate)+Iterno()-1) as Month

,Year($(vMinDate)+Iterno()-1) as Year

,MonthName($(vMinDate)+Iterno()-1) as MonthYear

,WeekName($(vMinDate)+Iterno()-1) as WeekYear

,WeekDay($(vMinDate)+Iterno()-1) as WeekDay

,WeekStart($(vMinDate)+Iterno()-1) as WeekStart

,QuarterName($(vMinDate)+Iterno()-1) as Quarter

AUTOGENERATE 1 WHILE RowNo() <= ($(vMaxDate)-$(vMinDate));

Ranges:

LOAD

Range

,date(evaluate(RangeStart)) as RangeStart

,date(evaluate(RangeEnd)) as RangeEnd;

LOAD * INLINE [

Range; RangeStart; RangeEnd;

This Week; WeekStart(vToday); WeekEnd(vToday);

Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1);

Last Two Weeks; WeekStart(vToday,-2); WeekEnd(vToday,-1);

]

(delimiter is ';');

JOIN (Ranges) IntervalMatch (InvoiceDate) LOAD RangeStart, RangeEnd RESIDENT Ranges;

However, I need something that will only count weekdays, excluding holidays. In addition, for a few of the holidays (like Thanksgiving, etc) don't always fall on the same date, but rather on the same day (third Thursday of the year, etc).

Any help would be greatly appreciated. Thank you!!

Re: Help creating Calendar that excludes weekends and holidays

Gysbert_Wassenaar — Fri, 02 Aug 2013 14:17:55 GMT

I suggest leaving the weekends and holidays in your calendar and use the networkdays function to calculate the number of workdays. See this blog post for more information: http://yahqblog.blogspot.nl/2010/09/qlikview-working-day-functions.html

This discussion may be of interest too: http://community.qlik.com/thread/13984

Note, there is no function that returns holidays or can tell you if a date is a holiday. Holidays differ very much per country/region. You'll have to find a list of holidays that apply to your situation somewhere and load those into qlikview.

Re: Help creating Calendar that excludes weekends and holidays

Tue, 29 Apr 2014 02:01:16 GMT

That's quite an innovative question I'd ever seen on the making of the calendar control. Though I can't offer any good solution, I also feel curious on this topic. Hide weekends in calendar are some features of the control, don't know if these kind of function will contribute goods to this question or not.