2 Replies Latest reply: Feb 8, 2018 10:08 AM by AJ Cunningham RSS

    Adapting a fiscal calendar to the new Qlik Sense auto calendar code

    Fred Amponsah

      If you had a fiscal calendar, say, your year starts on 28th of December, 2015 and ends on 25th of December, 2016. How are you going to incorporate that into the qlik sense auto calendar? And make sure the months, week and quarters correspond too. I would think the  "Addmonth" and "Makedate" functions could be used, but I have not been successful in using those. Any help, insights or ideas?

       

      Table with Fiscal calendar 

       

      Begin DateWeek NumberMonth NumberQuarter NumbYear
      12/28/20151112016
      1/25/20165212016
      2/29/201610312016
      3/28/201614422016
      4/25/201618522016
      5/23/201622622016
      6/27/201627732016
      7/25/20163183

       

       

      2016

      8/29/20163693

       

       

      2016

      9/26/201640104

       

       

      2016

      10/24/201644114

       

       

      2016

      11/28/201649124

       

       

      2016

      12/26/20161112017

       

      Auto Calendar Code:

       

        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

        Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

        Month($1) AS [Month] Tagged ('$month', '$cyclic'),

        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

        Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

        Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

        Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');

       

       

      DERIVE FIELDS FROM FIELDS [StartDate] USING [autoCalendar] ;

       

       

       

      I am new to qliksense, so pardon me.I attached a file with the full length of the calendar if needed.

        • Re: Adapting a fiscal calendar to the new Qlik Sense auto calendar code
          Elena Kusterer

          I am having the same problem. My fiscal year starts in October so while the auto calendar is great I really can't use quarters since they are for the regular year. I've tried using the FirstMonthOfYear function but was not successful.

          • Re: Adapting a fiscal calendar to the new Qlik Sense auto calendar code
            AJ Cunningham

            Similar issue here too.  You'd think it was about time that this Fiscal Calendar requirement became a standard feature / function within the Qlik Sense tool set, especially for Enterprise analysis.  In its simplest form I would have thought that an 'Auto Fiscal Calendar' capability could be available, perhaps just set the start of the fiscal year and the remaining Fiscal Calendar Measures  are created?

             

            Can you imagine a world were:

             

            [FiscalYear]                         [FiscalQuarter]

            [FiscalYearQuarter]             [FiscalMonth]

            [FiscalYearMonth]               [FiscalWeek]

            [InFiscalYTD]                      [FiscalYearsAgo]

            [InFiscalQTD]                      [FiscalQuartersAgo]

            [InFiscalMTD]                      [FiscalMonthsAgo]

            [InFiscalWTD]                      [FiscalWeeksAgo]

             

            are just a parameter or two away, no, just me?