topic Re: Pick maximum result aggregated by year in Reporting Service & Alerting

Pick maximum result aggregated by year

jorditorras — Fri, 15 Nov 2024 21:06:54 GMT

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!

Re: Pick maximum result aggregated by year

Anil_Babu_Samineni — Mon, 12 Feb 2024 16:31:39 GMT

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

Re: Pick maximum result aggregated by year

JonnyPoole — Tue, 13 Feb 2024 04:02:24 GMT

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/BasicAggregationFunctions/max.htm

Re: Pick maximum result aggregated by year

jorditorras — Wed, 14 Feb 2024 09:57:06 GMT

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

Re: Pick maximum result aggregated by year

jorditorras — Wed, 14 Feb 2024 09:58:25 GMT

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!

Re: Pick maximum result aggregated by year

vincent_ardiet_ — Wed, 14 Feb 2024 10:10:38 GMT

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

Re: Pick maximum result aggregated by year

theoat — Wed, 14 Feb 2024 10:46:31 GMT

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.

Re: Pick maximum result aggregated by year

JonnyPoole — Wed, 14 Feb 2024 13:51:32 GMT

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.