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

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

zjadoonj — Sat, 16 Nov 2024 00:55:46 GMT

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,

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

Vegar — Wed, 01 Apr 2020 17:18:48 GMT

Try something like this

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

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

zjadoonj — Thu, 02 Apr 2020 16:03:17 GMT

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,

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

Vegar — Thu, 02 Apr 2020 20:27:06 GMT

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

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

zjadoonj — Fri, 03 Apr 2020 13:55:55 GMT

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,

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

Vegar — Fri, 03 Apr 2020 15:04:38 GMT

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

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

zjadoonj — Mon, 06 Apr 2020 14:02:43 GMT

Thats great. Thanks so much Vegar.

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

zjadoonj — Tue, 14 Apr 2020 18:41:30 GMT

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,

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

Vegar — Tue, 14 Apr 2020 20:04:57 GMT

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

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

zjadoonj — Wed, 15 Apr 2020 17:48:00 GMT

Thats great. Thank you so much.

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

sunnywilson09 — Thu, 30 Apr 2020 20:28:43 GMT

Thanks so much for all the speedy replies everyone, I appreciate it so much. mobdro