Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
thkarner
Partner - Creator III
Partner - Creator III

Derived Calendar with Fiscal Year

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

1 Solution

Accepted Solutions
thkarner
Partner - Creator III
Partner - Creator III
Author

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

View solution in original post

5 Replies
thkarner
Partner - Creator III
Partner - Creator III
Author

Anybody who can help with this?

kenneth_morris
Partner - Contributor
Partner - Contributor

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
Contributor
Contributor

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
Partner - Contributor
Partner - Contributor

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

Qlik Derived FY.png

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.

Qlik Assets Panel.png

thkarner
Partner - Creator III
Partner - Creator III
Author

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