Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
msawyercke
Contributor

Fiscal Year definition

Good Morning,

I'm having problems with Fiscal Year definition.

I have the following dates that define our Fiscal Year calendar over the next few years:

Capture.JPG

Given these dates, what would be an example of the expression that would define Fiscal Years in the autoCalendar section in my data load editor?

Many, many thanks to whomever can guide me in the correct direction. 

1 Solution

Accepted Solutions
jens_leysen
New Contributor III

Re: Fiscal Year definition

Since your starting date and ending date vary each year, I would just put them in manually.

Try this:

IF(TempDate > 1/27/2015 AND TempDate < 1/25/2016 , 'FY2016',

IF(TempDate > 1/26/2016 AND TempDate < 1/30/2017 , 'FY2017',

IF(TempDate > 1/31/2017 AND TempDate < 1/29/2018 , 'FY2018',

IF(TempDate > 1/30/2018 AND TempDate < 1/28/2019 , 'FY2019',

IF(TempDate > 1/29/2019 AND TempDate < 1/27/2020 , 'FY2020',

IF(TempDate > 1/28/2020 AND TempDate < 1/25/2021 , 'FY2021',

IF(TempDate > 1/26/2021 AND TempDate < 1/31/2022 , 'FY2022', ))))))) AS [Fiscal Years]

Only thing here is that you should place the matching condition in the same format as your TempDate and after 2022 you will have to type the next years in the script manually again.

Let me know if this works.

4 Replies
aarkay29
Valued Contributor

Re: Fiscal Year definition

try this

if(TempDate > Weekstart(MonthEnd( Makedate(Year(TempDate),01))), Year(TempDate), Year(TempDate)-1) AS FiscalYear,

msawyercke
Contributor

Re: Fiscal Year definition

Thanks for your reply, Aar.  But, I don't understand where the 'begin' dates and 'end' dates are used in that expression...

Would you be so kind as to spell that out?

jens_leysen
New Contributor III

Re: Fiscal Year definition

Since your starting date and ending date vary each year, I would just put them in manually.

Try this:

IF(TempDate > 1/27/2015 AND TempDate < 1/25/2016 , 'FY2016',

IF(TempDate > 1/26/2016 AND TempDate < 1/30/2017 , 'FY2017',

IF(TempDate > 1/31/2017 AND TempDate < 1/29/2018 , 'FY2018',

IF(TempDate > 1/30/2018 AND TempDate < 1/28/2019 , 'FY2019',

IF(TempDate > 1/29/2019 AND TempDate < 1/27/2020 , 'FY2020',

IF(TempDate > 1/28/2020 AND TempDate < 1/25/2021 , 'FY2021',

IF(TempDate > 1/26/2021 AND TempDate < 1/31/2022 , 'FY2022', ))))))) AS [Fiscal Years]

Only thing here is that you should place the matching condition in the same format as your TempDate and after 2022 you will have to type the next years in the script manually again.

Let me know if this works.

aarkay29
Valued Contributor

Re: Fiscal Year definition

Hi Mike,

     The above expression would work if you are using a Master Calendar.

Not sure if you can use this in auto calendar script.