Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anisha_nan
New Contributor

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
Valued Contributor

Re: Need to calculate Fiscal Year for last three year

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
New Contributor

Re: Need to calculate Fiscal Year for last three year

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

 

Tags (1)
Shubham_Deshmukh
Valued Contributor

Re: Need to calculate Fiscal Year for last three year

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