Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
h2bi
Partner - Contributor III
Partner - Contributor III

Fiscal year KPI visual problem

Hello, i'm in a Qlik sense app that analyzes sales in each fiscal year/month

The fiscal year is from November to October, for example the sales of the 22/23 fiscal year (Nov 22 to Oct 23) reached 38m

While the current fiscal year 23/24 (Nov 23 to current Jan 24) for example made 3m$

I have a KPI visual that contains sum of sales in the current fiscal year, so as you can see in the image the current FY 23/24 we have 3m

-Sales in current FY 23/24    (Capture taken on January 2024):

h2bi_0-1704959353073.png

-The previous fiscal year 22/23 we did 38m sales

h2bi_1-1704959434804.png

 

The problem:

The issue is when we were in November or Dicember 2023 which was the FY 23/24, the KPI showed sales from Nov 23 the FY 22/23 + Nov and Dic 2023 (which was the 23/24 FY), so when we are in November or Dicember of any year it will always show the natural year sales untill we enter Jan 24 then it will shows the FY 23/24 sales so Nov Dic 2023 and Jan 2024 sales

-Capture taken on Dicember 2023 (FY 23/24) it shows 38m (22/23 FY sales) + 3m (23/24 FY sales) :

h2bi_2-1704959754917.png

I hope i have explained well, now this is the measure that calculates the total sales of each FY:

({<Period = {">=$(vMinPeriodoActual)<=$(vPeriodoActual)"}>}(Sum(sales)))

+
({<Period = {">=$(vMinPeriodoActual)<=$(vPeriodoActual)"}>}(Sum([refunds])))

 

Variables used:

vMinPeriodoActual = min({<ExerciseNum={$(vAñoActual)}>}Period)

           vAñoActual=max(Year)

vPeriodoActual = max(Period)

 

Columns used:

Period is the equivalent month number to the month name: AutoNumber(AñoMesNum) as Period

ExerciceNum is the fiscal year (for example we are in Nov 2017 so 2018): 

(year(addmonths(makedate(left(AñoMesNum,4),num(right(AñoMesNum,2),'00')),-10))+1) as ExerciseNum

 

Any idea or anything that can help me make the KPI to show the current fiscal year sales when a new fiscal year enters in Nov or Dic and not untill the natural year enters in Jan?

Labels (1)
1 Solution

Accepted Solutions
h2bi
Partner - Contributor III
Partner - Contributor III
Author

Hello

It is solved now, instead of making vAñoActual take max of the fiscal year which is Exercice field it was taking it from the Year field which contained the natural year

Thanks

View solution in original post

2 Replies
Or
MVP
MVP

Typically the easiest way to address fiscal years (or any other form of year that isn't "standard" is to use the third parameter in the YearName() function rather than using Year():

Or_0-1704981157912.png

Once you construct a field with the appropriate Year values where first month of year is set to 11, you can use that field for your assorted calculations rather than the regular Year.

h2bi
Partner - Contributor III
Partner - Contributor III
Author

Hello

It is solved now, instead of making vAñoActual take max of the fiscal year which is Exercice field it was taking it from the Year field which contained the natural year

Thanks