Fiscal Year Calculation in KPI

[Tejas]

Hi all,

I am new to Qlik Sense. I have a data set where I have transactions and fiscal years like 2016-2017, 2017-2018 etc...and each transaction is having a date. And my fiscal year starts with April and ends with March. So What I want to show in Qlik Sense is, if I select a year in filter as 2017-2018 then It should show me the Quantity of April 2017- March 2018 in one KPI and Quantity of Previous year that is  April 2016- March 2017 in other KPI as Previous Year. So how can i approach this? I have used sum(QTY) in 1st KPI but how about Previous Year's KPI ? How to write set analysis expression for that ? My fields are Financial Year, QTY which I want show in the end. So Basically I need two KPI's- one for Current Selected Year and One for Previous Year as selected in filter. Note that I have 2016-2017, 2017-2018 values as my Financial Year field not like 2016, 2017....Thanks in advance. 

[Vegar · Accepted solution]

Assuming you want to calculate SUM(Amount) and have a date field called [Date] then try something like this.

This fiscal year

Sum({<FiscYear=, [Date] = {">$(=yearstart(max([Date]), 0,4)) <$(=yearend(max([Date]), 0,4)) "} >} Amount) 

Previous f year

Sum({< FiscYear=, [Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount) 

[Tejas]

Thanks a lot Vegar, it is working. 

[Tejas]

Hi Vegar, in addition to yesterday's post I have one more filter as month. It is showing the specific values particularly present in that month. But somehow if i am selecting July month then it is showing me April month's values. It is showing me the month values as(selected month-3). I haven't changed any format or expressions to get this output. Attaching some photos of it if that can help. Thanks

[Vegar]

It's hard to pinpoint by the images you posted, but my best guess is that it has to do with your fiscal month. I notice that April is sorted first and I assume it got the numeric numeric value of one, July would then be four which would be equivalent to April in a normal calendar year.

What do you think about my theory? Is this pointing you into a solving direction?

[Tejas]

I got your point. How should I change it then? Because I haven't done the Fiscal Month calculation anywhere just tried FirstMonthOfTheYear=4 in our main script. I am attaching some photos of main and auto generated scripts, so you can get a better idea of it. Thanks for your quick response and looking forward to get an answer from you. ThanksFIrstMonthOfYear=4

[Kenji_Masicat]

Hi Vegar,

I tried your formula for FY  Sum({< FiscYear=, [Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount) 

but got the result of Jan22.

Can you share inputs please? Thanks a lot.

Kenji

[Vegar]

do you make any master calendar selections other than FiscYear and Date? Eg [Month] and [Week]. If so then you would need to clear them out with additional modifiers  like this:

Sum({< FiscYear=, Month=, Week=,[Date] = {">$(=yearstart(max([Date]), - 1,4)) <$(=yearend(max([Date]), - 1,4)) "} >} Amount)