Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Adapting a fiscal calendar to the new Qlik Sense auto calendar code

If you had a fiscal calendar, say, your year starts on 28th of December, 2015 and ends on 25th of December, 2016. How are you going to incorporate that into the qlik sense auto calendar? And make sure the months, week and quarters correspond too. I would think the  "Addmonth" and "Makedate" functions could be used, but I have not been successful in using those. Any help, insights or ideas?

Table with Fiscal calendar 

Begin DateWeek NumberMonth NumberQuarter NumbYear
12/28/20151112016
1/25/20165212016
2/29/201610312016
3/28/201614422016
4/25/201618522016
5/23/201622622016
6/27/201627732016
7/25/20163183

2016

8/29/20163693

2016

9/26/201640104

2016

10/24/201644114

2016

11/28/201649124

2016

12/26/20161112017

Auto Calendar Code:

  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 [StartDate] USING [autoCalendar] ;

I am new to qliksense, so pardon me.I attached a file with the full length of the calendar if needed.

2 Replies
kusterere
Contributor
Contributor

I am having the same problem. My fiscal year starts in October so while the auto calendar is great I really can't use quarters since they are for the regular year. I've tried using the FirstMonthOfYear function but was not successful.

Anonymous
Not applicable
Author

Similar issue here too.  You'd think it was about time that this Fiscal Calendar requirement became a standard feature / function within the Qlik Sense tool set, especially for Enterprise analysis.  In its simplest form I would have thought that an 'Auto Fiscal Calendar' capability could be available, perhaps just set the start of the fiscal year and the remaining Fiscal Calendar Measures  are created?

Can you imagine a world were:

[FiscalYear]                         [FiscalQuarter]

[FiscalYearQuarter]             [FiscalMonth]

[FiscalYearMonth]               [FiscalWeek]

[InFiscalYTD]                      [FiscalYearsAgo]

[InFiscalQTD]                      [FiscalQuartersAgo]

[InFiscalMTD]                      [FiscalMonthsAgo]

[InFiscalWTD]                      [FiscalWeeksAgo]

are just a parameter or two away, no, just me?