topic AW:Re: AW:Re: Maximum aggregation Value in QlikView

Maximum aggregation Value

Thu, 08 Oct 2009 18:15:27 GMT

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

Maximum aggregation Value

Thu, 08 Oct 2009 19:03:46 GMT

Hi Andi,

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

Good luck!

Rainer

Maximum aggregation Value

Oleg_Troyansky — Thu, 08 Oct 2009 19:05:22 GMT

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!

Maximum aggregation Value

stephencredmond — Thu, 08 Oct 2009 19:08:56 GMT

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

AW:Re: Maximum aggregation Value

Thu, 08 Oct 2009 19:23:18 GMT

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!!

AW:Re: Maximum aggregation Value

Thu, 08 Oct 2009 19:32:26 GMT

Andreas,

as you see i added 50.000.

Rainer

AW:Re: Maximum aggregation Value

Thu, 08 Oct 2009 19:36:37 GMT

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

AW:Re: Maximum aggregation Value

stephencredmond — Thu, 08 Oct 2009 19:57:36 GMT

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

AW:Re: AW:Re: Maximum aggregation Value

Thu, 08 Oct 2009 20:25:03 GMT

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

AW:Re: AW:Re: Maximum aggregation Value

stephencredmond — Thu, 08 Oct 2009 20:34:36 GMT

Hi,

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

is just the same as

Sum(TOTAL Sales)

See attached.

Stephen

AW:Re: Maximum aggregation Value

Thu, 08 Oct 2009 22:50:57 GMT

In your excel example a couple posts up, you show a row consisting of the max value repeated over all months. Are you trying to show what Sales would have been if all months were the max value? If so, your formula would have to look like this:

=Sum(aggr(Max(Total aggr(Sum(Sales), Month)), Month))