Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
anisha_nan
Contributor II
Contributor II

Need to calculate Fiscal Year for last three year

Hi,

I have one Period field on the basis of that I want to load data always for last three fiscal years in script.

Period field has value in it as 201906 means YYYYMM

My Fiscal year start from 1.10.2016 and end 30.09.2017

I have created one variable for PPerviousfiscal year

let  vPPerFiscalYear = If(Month(Today()) >=10 , Year(Today()),Year(Today() -3));

when I use this variable its only shows me three months data of 2019 rather it should show me last three years data i.e 2016-oct,2017,2018 and 2019.

Can you please help me out how can I achieve to get three years fiscal year data.

Regards

Labels (1)
3 Replies
Shubham_Deshmukh
Specialist
Specialist

Hi @anisha_nan ,

Check this correction in variable exp, but this will also give you only 2016, I dnt know how you are using this for your requirement but look at this correction,

Your exp  - let  vPPerFiscalYear = If(Month(Today()) >=10 , Year(Today()),Year(Today() -3))

Correction -  let  vPPerFiscalYear = If(Month(Today()) >=10 , Year(Today()),Year(Today()) -3)

Try this, if if doesn't work we will try some thing else.

Rgds,

Shubham

 

anisha_nan
Contributor II
Contributor II
Author

Hi @Shubham_Deshmukh ,

Thanks Shubham for the reply...

I tried with the below expression which you provided it picks data only for 1 year and not for last fiscal three years.

Correction -  let  vPPerFiscalYear = If(Month(Today()) >=10 , Year(Today()),Year(Today()) -3)

Ideally as per the requirement It should pick data for 2016 oct till current date .

PPFY - 2016-2017 PFY - 2017-2018  Current Year - 2018-2019.

Please provide some other workaround if possible .

Regards

 

Shubham_Deshmukh
Specialist
Specialist

Thats what I said I dont have idea where you were using that value because it will give only only value.

If you want data only from Oct - (currentYear -  3) year, then use date range ,

=yourDate >= Date(date#('01-10-'&(year(today())-3),'DD-MM-YYYY')) and yourDate <= date(today()).

Rgds,

Shubham