Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks, how would i be able to link it in? Do I require a plugin for the API?
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;
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
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.