Working master calendar

[james_hanify]

Hi,

I have been looking at many master calendar lists and none of them seem to be fully working how i think they should.

I am using qlikview 11, though I am seeing with our provider whether I can upgrade as I believe there are new functions I can use.

However, i've noticed a lot of them seem to have some errors, quite notably that 01/01/2016 to 03/01/2016 is being considered week 53 of 2015, this is not correct.

The fields I would really like are:

Date,

Month,

Year

Week of the year

Week of the month

Financial Quarters set from April

Year Quarters

Month

YYYYMM

UK working days (probably impossible but anyway) in a week

Many thanks.

James

[james_hanify]

Thanks, how would i be able to link it in? Do I require a plugin for the API?

[jonathandienst]

No, just load the holidays like this - adjust the country name to your requirement:

//----------------------------------------------------------------------------------------------------

// Get holiday dates from timanddate.com

//----------------------------------------------------------------------------------------------------

Let zMinYear = Year(YearStart(zToday, -5));

Let zMaxYear = Year(zTo);

For zi = zMinYear to zMaxYear

  tmpHolidays:

  LOAD Weekday,

  Date(Date#(Date & ' ' & $(zi), 'MMM D YYYY')) As Date,

  [Holiday name]

  FROM

  [http://www.timeanddate.com/holidays/south-africa/$(zi)]

  (html, codepage is 1252, embedded labels, table is @1)

  Where [Holiday type] = 'Public Holiday';

  ;

Next

tmpConcatHolidays:

LOAD chr(39) & Concat(Date, chr(39) & ',' & chr(39)) & chr(39) As HolidayList

Resident tmpHolidays;

Let vHolidayList = Peek('HolidayList');

Let vHolidayList = If(Len(vHolidayList) > 0, vHolidayList, '0');

DROP Tables tmpHolidays, tmpConcatHolidays;

Then use the variable to create working days fields like this:

//----------------------------------------------------------------------------------------------------

// Build calendar - partial script

//----------------------------------------------------------------------------------------------------

tmpCalendar:

LOAD

  Date($(zTo) - RowNo()) As CalDate

AutoGenerate($(zTo) - $(zFrom));

Calendar:

LOAD *,

  If(Match(WeekDay, 'Sat', 'Sun') Or Match(CalDate, $(vHolidayList)), 0, 1) As WorkingDay,

  If(Match(WeekDay, 'Sat', 'Sun'), 'Weekend', If(Match(CalDate, $(vHolidayList)), 'Holiday', 'Workday')) As DayType,

  ....

;

LOAD CalDate,

  RangeMax(1, NetWorkDays(MonthStart(CalDate), CalDate, $(vHolidayList))) As WorkDayofMonth,

  NetWorkDays(MonthStart(CalDate), MonthEnd(CalDate), $(vHolidayList)) As MonthWorkDays,

  NetWorkDays(CalDate, MonthEnd(CalDate), $(vHolidayList)) As WorkDaysToMonthEnd,

  ....

Resident tmpCalendar;

Drop Table tmpCalendar;

[james_hanify]

Wow this seems really neat, it is giving me a

Script line error:

For zi = zMinYear to zMaxYear

Though, so I presume it is not taking it as a variable, tried doing $(zMinYear) and no cigar

[lucasmagalhaes]

Hi, James.

I usually use this tutorial to help me with master calendar

Link: Master Calendar by Qlik

Generating Missing Data In QlikView

Let me know if it helps you.

Lucas Magalhães.

Paralelo CS