Fiscal Year

[Manasareddy09]

Hello Qlik Enthusiasts,

I have a question regarding, assigning the Fiscal Year, I have the following fields ,

InvDate,

Scheduled_InvDate,

InvStatus

Using the below formula to assigned a fiscal year,

=If (InvStatus = 'Accepted' or InvStatus = 'Not Accepted' or InvStatus = 'ON_PROG'  or ( InvStatus = 'PLANNED'  and ISNULL(InvDate = 0),

if(InvDate >= date(45566)   //date(45566)=10/01/2024

and InvDate < date(45931),   //date(45931)=10/01/2025

'FY2025',

if(InvDate >= date(45200)   //date(45200)=10/01/2023

and InvDate < date(45931),   //date(45566)=10/01/2024

'FY2024',

......

f(InvDate >= date(42278)   //date(42278)=10/01/2015

and InvDate < date(42644),   //date(42644)=10/01/2016

'FY2016,'<FF16'))))),

if(Scheduled_InvDate, >= date(45566)   //date(45566)=10/01/2024

andScheduled_InvDate,< date(45931),   //date(45931)=10/01/2025

'FY2025',

if(Scheduled_InvDate, >= date(45200)   //date(45200)=10/01/2023

and Scheduled_InvDate, < date(45931),   //date(45566)=10/01/2024

'FY2024',

......

f(Scheduled_InvDate, >= date(42278)   //date(42278)=10/01/2015

and Scheduled_InvDate, < date(42644),   //date(42644)=10/01/2016

'FY2016,'<FF16')))))

)

This shows me the result i want to see, But i know this is not the best practice in giving everything manually. I want to know if there is a better way of doing it. 

Could we do it with a variable ? by giving default Month and day(11th) and just changing the year according to the InvDate ? 

Like if InvDate year is 2021, then we display 'FY(Year+1)'.. something like this. I dont know if we could implement this but please give me suggestions.

I would be a great help ,

Thank you.

[Manasareddy09]

Hello Everyone,

I was able to achieve this by using the following functions ,

Make Date() --- To set a particular date,if you fiscal year starts on 5th or 10 th of the month

YearName() ---- If you give a particular date , I.e. InvDate = 10-10-2021, and your fiscal year starts in 9th month of every year, This functions gives you the output of as folows:  '2021-2022'.

YearName(InvDate, 0 , 9) as FiscalYear;

Here 0 indicates the current year of the Given date . Incase you want to get next year of the fiscal year, which is '2023' according to the above example, You have to give 1 instead of 0. 

And to get previous year it is Viceversa, -1 instead of 0 . 

Thank you,

Happy coding 🙂