5 Replies Latest reply: Mar 31, 2017 11:47 AM by Thomas Karner RSS

    Derived Calendar with Fiscal Year

    Thomas Karner
    Thomas Karner Jan 23, 2017 1:23 PM

      Hi,

       

      in case of a date field is loaded into the data model Qlik Sense automatically creates a script to derive some calendar date fields, which can also be used for the contious timeline in the line chart.

      This is the generated code:

       

      [autoCalendar]:
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  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 [Sales Order Date], [Sales Ship Date], [Sales Close Date] USING [autoCalendar] ;

       

      I´ve also to provide the following fiscal year fields additionally (FY = Fiscal Year):

      - FY Year

      - FY Quarter

      - FY YearQuarter

      - FY Month (sorted beginning with first month of the fiscal year)

      - FY YearMonth

       

      How must the script look like to derive the fields including support of the the line chart with the continous timeline to zoom in and out.

       

      Thanks for your help!

      Thomas