topic Re: Derived Calendar with Fiscal Year in App Development

Derived Calendar with Fiscal Year

thkarner — Mon, 23 Jan 2017 18:22:37 GMT

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

Re: Derived Calendar with Fiscal Year

thkarner — Fri, 27 Jan 2017 10:37:59 GMT

Anybody who can help with this?

Re: Derived Calendar with Fiscal Year

kenneth_morris — Mon, 27 Feb 2017 15:28:42 GMT

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

Re: Derived Calendar with Fiscal Year

kusterere — Thu, 23 Mar 2017 13:55:05 GMT

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.

Re: Derived Calendar with Fiscal Year

kenneth_morris — Thu, 23 Mar 2017 14:55:28 GMT

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.

Re: Derived Calendar with Fiscal Year

thkarner — Fri, 31 Mar 2017 15:47:37 GMT

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