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

[MK_QSL]

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

[james_hanify]

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

[MK_QSL]

OK and what output you are looking for?

Can you provide in excel file here?

[james_hanify]

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

[MK_QSL]

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.

[jonathandienst]

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).

[james_hanify]

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

[james_hanify]

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?

[jonathandienst]

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