13 Replies Latest reply: Nov 25, 2016 12:27 PM by Lucas Magalhães RSS

    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

        • 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

               

               

              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
                  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
                      01/01/2016January201611201601FridayTRUE0
                      04/01/2016January201622201601MondayTRUE5
                        • 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

                                            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
                            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 Magalhães

                              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