Pick maximum result aggregated by year

[jorditorras]

Dear Gurus, 

I have a Bar Chart with a KPI and a Dimension of "Year". I want to set the "Y" axis with the higher value of all the last 3 years. If, for example, my results are like:

KPI 2024	1500
KPI 2023	2500
KPI 2022	3000

Then, I need to set the Maximum "Y" axis as 3000. Any Idea how can I try this? 

I've tried 2 opcions but when I filter per one single year they doesn't work:

1. Max(Aggr((KPI), Year))

2. RangeMax(KPI2024, KPI2023, KPI2022)

I prefer not to use to use "IF" formulas if possible as each KPI is a bit complex and it can get a bad performance.

Thanks for your help! 

[Anil_Babu_Samineni]

@jorditorras How the result came to tabular already? If they just source, Why not simply with Max(Your Field name)?

[JonnyPoole]

What is the field name?  if the field name is KPI then try  Max( [KPI] )  or  Max( total [KPI] ).   The total qualifer ensure the max is applied while looking at all the dimensional values for the dimension used in the chart.  The full explanation of the function and usage and examples are here  https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/BasicAggr... 

[jorditorras]

Hi Anil, the thing is that I don't need the Max(Field Name), but instead, the Maximum KPI of all the past years. 

[jorditorras]

KPI i mean "Measure". So, I'm searching the Maximum result of "Measure" among the current and the past "n" Years.

Thanks for  your comment!

[vincent_ardiet_]

You want you maximum to be always 3000 whichever fields your are selecting?
Something like this should work: {1}Max(Aggr((KPI), Year))

[theoat]

You can use a rank in the set analysis of your KPI.
If you KPI is count(sales), you will have :
count({<Year = {"=Rank([KPI])=1"}>} sales)

Kind regards,
THEO ATRAGIE.

[JonnyPoole]

If there are only 2 columns in your data set :  [Year] and [Measure]  .. then you would be using the following expression:  Max( total [Measure])

I added the expression both as a reference line and as a Y axis maximum in this chart.  Actually I put the Y axis maximum to be 10% higher:   Max( total [Measure]) + (.1 * Max( total [Measure]) ) so that you can have some space at the top to see everything.  

Take a look and validate this is indeed your data set and use case.