13 Replies Latest reply: Nov 25, 2016 12:27 PM by Lucas Magalhaes RSS

    Working master calendar

    James Hanify



      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:





      Week of the year

      Week of the month

      Financial Quarters set from April

      Year Quarters



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


      Many thanks.



        • Re: Working master calendar
          Manish Kachhia

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

            • Re: Working master calendar
              James Hanify







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

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







              for x = vStartDay to vEndDate




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



              next x









                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
                  Manish Kachhia

                  OK and what output you are looking for?

                  Can you provide in excel file here?

                    • Re: Working master calendar
                      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



                      DateMonthYearWeek of YearWeekofMonthPeriodMonthWeekdayWorkingDaysWeekWorkingDays
                        • Re: Working master calendar
                          Jonathan Dienst

                          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

                              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
                                  Jonathan Dienst

                                  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

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

                                        • Re: Working master calendar
                                          Jonathan Dienst

                                          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


                                            LOAD Weekday,

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

                                            [Holiday name]



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

                                            Where [Holiday type] = 'Public Holiday';





                                          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




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

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



                                          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
                            Manish Kachhia

                            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
                              Lucas Magalhaes

                              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