Derived Calendar with Fiscal Year

[thkarner]

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

[thkarner]

Anybody who can help with this?

[kenneth_morris]

Hi,

Did you ever make any headway finding a good solution to this? I've been trying to to do something very similar using declare/derive. While I've gotten parts to work, the date functions don't return the expected values for every row and I've been trying to determine if I have done something wrong, there is a bug or what. I've posted also without much useful response.

Thanks,

Ken Morris

[kusterere]

If anyone figures this out it would be great! I would much rather be able to use the derived fields rather than copying and pasting a master calendar for each date field.

[kenneth_morris]

Well I did get another proof of concept to work for fiscal year.

I expect that this could be extended to include quarters, months etc and that the the fixed start month could be changed to a DECLARE parameter but I have not gotten to that yet. Loading from Data Load Manager worked but I didn't immediately notice that it had since I expected Fiscal Year to show up in the table view there which it does not. But in the sheet editor, if you open the fields section on the left assets panel, it appears as a subfield of Action Taken Date.

[thkarner · Accepted solution]

Hi, I did some intensive research on this topic connected also with the time-aware charts.

My findings could be found at

Derived Calendar Fields

Best,

Thomas