Get Fiscal year end date in a variable e.g 03/31/2020 is end of my Fiscal year - QlikView

[zjadoonj]

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,

[Vegar]

Try something like this

DayName(YearEnd( today(), 0, 4))

[zjadoonj]

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,

[Vegar · Accepted solution]

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

[zjadoonj]

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,

[Vegar · Accepted solution]

Yes it will change to 03-31-2021 as soon as your today() returns 04-01-2021.

[zjadoonj]

Thats great. Thanks so much Vegar.

[zjadoonj]

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,

[Vegar · Accepted solution]

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

[zjadoonj]

Thats great. Thank you so much.