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: 
msawyercke
Creator
Creator

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
Anonymous
Not applicable

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.

View solution in original post

4 Replies
aarkay29
Specialist
Specialist

try this

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

msawyercke
Creator
Creator
Author

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?

Anonymous
Not applicable

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

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.