2 Replies Latest reply: Mar 14, 2017 11:34 PM by Vikram Vangala RSS

    Master Calendar with a specific date as the start date of the year

      We would like to measure growth on a weekly basis based on last year numbers.

       

      I understand that we can dynamically change the start month of a fiscal year. But is there a way to create a master calendar with a specific date as the start date of the year?


      For instance, I would like to specify a precise date i.e. March 13, 2016, as my start date so according to my calendar -  week 1 starts from 13th - 19th October 2016 and subsequent weeks follow.


      Please kindly assist...


      I'm currently looking at this link https://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/weekstart.htm and I would like to use this logic to transform my date field to reference my baseline...

        • Re: Master Calendar with a specific date as the start date of the year

          Correction: week 1 starts from 13th - 19th March 2016

            • Re: Master Calendar with a specific date as the start date of the year
              Vikram Vangala

              I am not sure whether it would work for you.

               

              First create a normal calendar  table T1 like from Jan 1 to Dec 31 that links to you date field.

              In the same table include the "day number of the year" from 1 to 365.

              T1

              Date      Daynumbofyear     

              jan 1     1                            

              jan 2     2                            

              ...         365                        

               

              Now create a new table T2 starting from 13th March as first day of year.

              Increment it to 365 days so it will end at Mar 12 2017.

              Include the day number of the yesr (in this case it will be rowno() that increments to 365 days give this field name as "day number of the year").

              Use apply map to get the other values related to calendar using the "day number of the year" from table T1.

               

              T2

              DateNew      Daynumbofyear    

              Mar 13               1                   

              Mar 14              2                  

              ...                   365

               

              Now when you reload the data model.It will associate using "day number of the year".

              Then u can start using the T2 table fields as your master calendar in you comparisons.

               

              Best,

              Vikram