Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
thkarner
Contributor 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

Tags (2)
1 Solution

Accepted Solutions
thkarner
Contributor III

Re: Derived Calendar with Fiscal Year

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

5 Replies
thkarner
Contributor III

Re: Derived Calendar with Fiscal Year

Anybody who can help with this?

kenneth_morris
New Contributor

Re: Derived Calendar with Fiscal Year

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

Re: Derived Calendar with Fiscal Year

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

Re: Derived Calendar with Fiscal Year

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

Re: Derived Calendar with Fiscal Year

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