Maximum aggregation Value

[#1]

Hi all!

I'd like to dynamically change the scale of my axis by using the max min Values in the Axis Tab.

I think it would be the best to get the max of an aggregated Value.What I want to do is something like

max(aggr(sum(Sales), Month))

to get the highest Sales Value of an Month!!

Unfortunatly that doesn't work! Any Ideas?

Greetings

andi

[#2]

Hi Andi,

here is an idea (See the second sheet "Axes").

Good luck!

Rainer

[Oleg_Troyansky]

1. try adding a equal sign "=" in front of the formula

2. try using "total" qualifiers, to "break out" of the chart dimension values

I remember doing something similar once, and it worked for me. Once you make it work, you'll want to add a bit on top of the maximum, to allow the maximum value to be displayed nicely within the chart.

cheers!

[stephencredmond]

Hi Andreas,

First remember to take off the "Forced 0" checkbox in the Axes tab - otherwise the Min expression will not work.

Does that AGGR expression match your chart? I mean, is the chart dimension Month and the expression Sum(Sales)?

If it is then this should work to set the max / min of the chart. If not then you need to adjust the AGGR to match your chart - include all the dimensions that your chart uses.

You might also want to do something like multiply the Max by 1.01 and the Min by 0.99 - just so that you can see the Bar/line/etc..

Stephen

[#5]

Hi Rainer!

thanks for your answer. But as you can see on the screenshot the value isn't correct somehow. The value i looked for should be 204,679 on every month. But Thanks a lot anyway!!

[#6 · Accepted solution]

Andreas,

as you see i added 50.000.

Rainer

[#7]

Hi Stephen!

This brilliantly solved my axis Problem. Even though I got curious how to get the max aggregated values in General? Is there a SetAnalysis way?

It should do something like this for every Dimension

[stephencredmond]

I am not 100% sure what your question is here but I am guessing that when you put the expression:

Max(AGGR(Sum(Sales), Month))

into your straight table then you are seeing the exact same value as the Sum(Sales) for each month. This is because the AGGR is being calculated for each month within the chart - it "respects" the dimensions of the chart.

You need to add the TOTAL qualifier to have the AGGR ignore the dimension:

Max(AGGR(TOTAL Sum(Sales), Month))

Stephen

[#9]

Hi Stephen!

Thanks a lot for your help. The TOTAL Qualifier helped.

Even though for all the want to try that it has to be

Max(AGGR(Sum(TOTAL Sales), Month))

thanks to all

andi

[stephencredmond]

Hi,

Max(AGGR(Sum(TOTAL Sales), Month))

is just the same as

Sum(TOTAL Sales)

See attached.

Stephen