Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):
-The previous fiscal year 22/23 we did 38m sales
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) :
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?
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
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():
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.
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