
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Get Fiscal year end date in a variable e.g 03/31/2020 is end of my Fiscal year - QlikView
Hi Qlik Engineers,
I have a requirement to create a date variable for Fiscal year end date which always returns last day of fiscal year as our fiscal year starts from 4/1/20 so last day is (3/31/20) and should change next fiscal year dynamically. We use this variable while loading data for one of my script . Can someone please let me know how to create one?
before we were using standard calendar variable like below:
Let vEOYDate = date(num(firstworkdate(yearstart(firstworkdate(today(), 2), 0), 2)),'MM/DD/YYYY')
The above variable always returns Last day of previous Calendar year like 12/31/2019
but requirement got changed and request is to use fiscal year end date in the script instead calendar year. Can some one help what needs to be changed in the above variable to get Fiscal year end date?
Any help will be really appreciated.
Regards,
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What you are asking for is the the last day of the previous fiscal year. The current fiscal year is april 2020-march 2021.
You can do this by reducing the skew of yearend by period_no parameter to -1.
=DayName(YearEnd( today(), -1, 4))
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes it will change to 03-31-2021 as soon as your today() returns 04-01-2021.
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You will have this issue 03-31-2024 which is a Sunday. To get the previous workday you can use FirstWorkDate() function.
=firstworkdate( YearEnd( today(), 3, 4) ,1)
Hope this will solve your last issue.
BR
Vegar
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vegar,
Thanks for responding. your script is giving 03/31/2021 but I am looking for 03/31/2020 as the last day of current fiscal year. Can you please help getting 03/31/2020?
I really appreciate the help.
Regards,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What you are asking for is the the last day of the previous fiscal year. The current fiscal year is april 2020-march 2021.
You can do this by reducing the skew of yearend by period_no parameter to -1.
=DayName(YearEnd( today(), -1, 4))
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vegar,
That's awesome and that is what i was looking for. One more question, on Apr 1, 2021 when the next fiscal year start, it will dynamically change to 03-31-2021 right?
Regards,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes it will change to 03-31-2021 as soon as your today() returns 04-01-2021.
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thats great. Thanks so much Vegar.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vegar,
I ran into one more issue with this if you could help. Let say if 3/31/2021 is SAT or Sunday then we would like the variable to populate last working day of the week which is Friday 3/29/2021. Can you please help changing the variable according to this?
Thanks,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You will have this issue 03-31-2024 which is a Sunday. To get the previous workday you can use FirstWorkDate() function.
=firstworkdate( YearEnd( today(), 3, 4) ,1)
Hope this will solve your last issue.
BR
Vegar
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thats great. Thank you so much.

- « Previous Replies
-
- 1
- 2
- Next Replies »