topic Re: Working master calendar in QlikView

Working master calendar

james_hanify — Wed, 23 Nov 2016 10:29:44 GMT

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

Re: Working master calendar

MK_QSL — Wed, 23 Nov 2016 10:36:32 GMT

Can you provide sample data or dummy app to check what exactly you are looking for?

Re: Working master calendar

james_hanify — Wed, 23 Nov 2016 11:13:20 GMT

let vStartDay = date('01/01/2016');

let vEndDate = date('31/12/2016');

for x = vStartDay to vEndDate

tempCalendar:

load $(x) as Date AutoGenerate(1);

next x

NoConcatenate

Working_Calendar:

Load

Date,

weekday(Date) as WeekDay,

week(Date) as WeekOfYear,

if((week(Date) - week(monthstart(Date)) + 1)<1,1,(week(Date) - week(monthstart(Date)) + 1)) as WeekNumberInMonth,

Month(Date) as Month,

Year(Date) as Year,

ceil(month(Date)/3) as Qtr

resident tempCalendar

where WeekDay(Date) <> 'Sat' and WeekDay(Date) <> 'Sun';

drop table tempCalendar;

//

This is what I am currently using

Re: Working master calendar

MK_QSL — Wed, 23 Nov 2016 11:21:43 GMT

OK and what output you are looking for?

Can you provide in excel file here?

Re: Working master calendar

james_hanify — Wed, 23 Nov 2016 11:33:12 GMT

Hi Manish, I am literally just using it the calendar by itself, can't work out how you actually attach a file, but this is the format i'd like

Date	Month	Year	Week of Year	WeekofMonth	PeriodMonth	Weekday	WorkingDays	WeekWorkingDays
01/01/2016	January	2016	1	1	201601	Friday	TRUE	0
04/01/2016	January	2016	2	2	201601	Monday	TRUE	5

Re: Working master calendar

MK_QSL — Wed, 23 Nov 2016 11:50:04 GMT

Go to this link

Working master calendar

Now click on reply.

Click on

Use advanced editor

on top right corner...

Now you have Attach at the bottom right..

Attach your required output file here.

Re: Working master calendar

jonathandienst — Wed, 23 Nov 2016 11:53:05 GMT

There are many ways of handling weeks, especially over year end, so what is "wrong" with one methods may be correct using another. According to one commonly used international standard (ISO 8601), 28 December 2015 - 3 January 2016 is indeed week 53 of 2015. This, I think, is what is used by the Week and WeekYear functions in QV.

So those calendars are not "wrong" - but they may not be suitable for your requirements. You need to state clearly the rules you want to define the start of week1 of the year, wand what you want to do with the left over days (after 52 weeks and before week1).

Re: Working master calendar

james_hanify — Wed, 23 Nov 2016 12:32:30 GMT

Using that example, even with the 52 week thing that someone else mentioned, unfortunately my weeks is still wrong, I presume the formula must be wrong,

Just trying to test it for all scenarios I can

Re: Working master calendar

james_hanify — Wed, 23 Nov 2016 12:33:29 GMT

Hi Jonathan, you are right and in fact, it doesn't bother me too much that, my weeks are still out of whack though, i don't suppose doing bank holidays is possible without some kind of master spreadsheet?

Re: Working master calendar

jonathandienst — Thu, 24 Nov 2016 05:03:10 GMT

I include a working day indicator in my calendar that has a value of 1 for a workday and 0 for non-working (weekends and holidays). I load the holidays for this calculation from timeanddate.com - Holidays worldwide

Re: Working master calendar

james_hanify — Thu, 24 Nov 2016 10:43:28 GMT

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

Re: Working master calendar

jonathandienst — Thu, 24 Nov 2016 13:32:29 GMT

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;

Re: Working master calendar

james_hanify — Thu, 24 Nov 2016 15:17:07 GMT

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

Re: Working master calendar

lucasmagalhaes — Fri, 25 Nov 2016 16:58:19 GMT

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