0 Replies Latest reply: Sep 13, 2016 5:34 PM by Fred Amponsah 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.