6 Replies Latest reply: Aug 11, 2015 10:51 PM by May Javier RSS

    Set a different First Month of The Year

    May Javier

      Hello Qlik Community,

       

      I want to assign a different start month that would align to our Fiscal Year, ours starts every July.

      Are there other ways to change the first month of the year?

       

      The way I did is by SQL script like this one:

       

      .....CASE

           WHEN DATE(CONVERT_TZ(j.created_at,"+00:00","+10:00")) BETWEEN '2007-07-01' AND '2008-06-30' THEN 2008

           WHEN DATE(CONVERT_TZ(j.created_at,"+00:00","+10:00")) BETWEEN '2008-07-01' AND '2009-06-30' THEN 2009.....

      and

      .....CASE

           WHEN MONTH(CONVERT_TZ(j.created_at,"+00:00","+10:00")) =1 THEN 7

            WHEN MONTH(CONVERT_TZ(j.created_at,"+00:00","+10:00")) =2 THEN 8.....

       

      I am having a doubt that eventually this would lead to data errors.

       

      Thanks!

        • Re: Set a different First Month of The Year
          Rudolf Linder

          if you have a mastercalendar you may add columns for fiscal month, fiscal year (one way by inline table)

          in your Charts you use the fiscal month as dimension

          • Re: Set a different First Month of The Year
            Jonathan Dienst

            I use this code in my master calendar to get the Fiscal Year and Period from the calendar date (CalDate / CalMonth / CalYear):

             

            // Fiscal year fields - GL Year

            CalYear + If(CalMonth < $(zGLYearStartMonth), 0, 1) As GLYear,

             

            // Fiscal year fields - GL Period

            If (CalMonth >= $(zGLYearStartMonth),

              If(CalDay > $(zLastDOM), CalMonth - $(zGLYearStartMonth) + 2, CalMonth - $(zGLYearStartMonth) + 1),

              If(CalDay > $(zLastDOM), CalMonth + (12 - $(zGLYearStartMonth) + 2), CalMonth + (12 - $(zGLYearStartMonth) + 1)))   As GLPeriodM,

             

            The variables:

            • zGLYearStartMonth first month of fiscal year
            • zLastDOM last day of the month of fiscal period (set to 31 or greater if the fiscal periods correspond to calendar months)
            • Re: Set a different First Month of The Year
              Gareth Schoeman

              Hi,

               

              Attached is a calendar. in the calendar tab is a variable in which you change the first month of a fiscal year

               

               

              remember to set the Calendar Type to "always only 1 selected"

               

              essentially you are duplicating your master calendar for different calendar types and then selecting the calendar type you want to work with.

               

              Since all your time data calculations and graphs will work with the new YEAR and MONTH set up - things will automatically change based on the calendar type selections.

               

              hope this helps,

               

                Gareth

                • Re: Set a different First Month of The Year
                  May Javier

                  Hello Gareth, I cannot load .qvw format files. May I take a look at your script?

                    • Re: Set a different First Month of The Year
                      Gareth Schoeman

                      Hi May,

                       

                      you put the following field in your transactions table (or whatever table contains the date):

                      Year(date(SaleDate))&num(Month(date(SaleDate)),'00') as YM,

                       

                      then you can add this statement at the end of your load script (or anytime after the table containing YM)

                       

                      //Create fiscal year start month

                      set vFiscalMonth = 8;

                       

                       

                      //create temp calander for all the distinct year month dates in your data

                      TCAL:

                      load distinct YM,

                      num(right(YM,2)) as TMonth,

                      Month(MakeDate(Left(YM,4),right(YM,2) , 1)) As MONTH

                      Resident TRANSACTIONS;

                       

                       

                      CALENDAR:

                      load

                        YM,

                        num(left(YM,4)) as YEAR,

                        TMonth as MonthSort,

                        MONTH,

                        'Q'&(if(num(right(YM,2))<4,1,if(num(right(YM,2))<7,2,if(num(right(YM,2))<10,3,if(num(right(YM,2))<13,4))))) as Quarter,

                        'Calendar' as CalendarType

                      resident TCAL;

                       

                      //Duplicates calendar but for Fiscal Year

                      Concatenate(CALENDAR)

                      load

                        YM,

                        if(TMonth<$(vFiscalMonth),num(left(YM,4)),num(left(YM,4))+1) as YEAR,

                        if(TMonth<$(vFiscalMonth),TMonth+12-$(vFiscalMonth)+1,TMonth-$(vFiscalMonth)+1) as MonthSort,

                        MONTH,

                        'Q'&(if(num(right(YM,2))<4,4,if(num(right(YM,2))<7,1,if(num(right(YM,2))<10,2,if(num(right(YM,2))<13,3))))) as Quarter,

                        'Fiscal' as CalendarType

                      resident TCAL;

                       

                      Drop Table TCAL;

                       

                      Once you are using your data in the front end, remember to use YEAR and MONTH when you deal with date information.

                       

                      NB: create a listbox for CalendarType - select 1 , then right click properties and change to "Always only 1 selected" - this is because multiple calendars duplicate data due to there being both a Fiscal YEAR MONTH  and a Calendar YEAR MONTH pointing to the same data so you want to restrict it to only 1 set at all times.

                       

                      hope this helps.. let me know if you have problems

                       

                        Gareth

                       

                      p.s. The MonthSort field is there for if you want to make the list boxes sort the order based on the calendar type. so for fiscal it will go month AUG,SEP,OCT,NOV,DEC,JAN,...JUL and for normal Calendar it will go JAN,FEB,MAR...DEC - also handy for when you make charts.

                  • Re: Set a different First Month of The Year
                    May Javier

                    Thank you all for the time to respond to my query! I found a helpful blog from HIC Fiscal Year. It worked.