Question Re: Max value from a list in Technology Q&A

Max value from a list

Former Member — Tue, 31 Jan 2012 18:56:19 GMT

What would be the fastest and easiest way to find the maximum value from a list of a key-figure like, lets say Monthly Sales?

Re: Max value from a list

anindya_bose — Tue, 31 Jan 2012 19:08:23 GMT

Create a condition and see TOP N ( N = number of top values , like top 5, top 10 etc)

Re: Max value from a list

Former Member — Tue, 31 Jan 2012 19:20:27 GMT

I don't want a ranked list, I just want to pick the maximum value from a list and report it in the next column. Would using the MAX mathematical function work?

Re: Max value from a list

anindya_bose — Tue, 31 Jan 2012 19:41:46 GMT

Yes...

Create a formula and use that MAX function if you just need the Max value

Regards

Anindya

Re: Max value from a list

Former Member — Tue, 31 Jan 2012 19:47:24 GMT

Can u give me the steps? plz

Re: Max value from a list

anindya_bose — Tue, 31 Jan 2012 20:02:54 GMT

I think we do not need MAX function here.

Create a Formula --->Insert your Key Figure for which you want to do this

Save the formula.

Come to Aggregation tab ( right side pane) --->Under Exception Aggregation select Maximum

It should be OK

Regards

Anindya

Re: Max value from a list

Former Member — Tue, 31 Jan 2012 20:20:47 GMT

Hi Anindya,

Thanks for the input, I tried your solution but do we have to put anything in the Ref.Characteristic box.

Also I used your solution, created a formula and then created a condition (TOP N, 1) on that formula. That does give me the maximum value but in the output display all the other rows are also diminished. The way I want my result to look is somethign like this

Day/Month Sales Amt Max

1 100

2 75

3 120

4 65

5 80 120

Something like that. Or some functionality where after executing the report the end user can click on s omething and get the max value.

Re: Max value from a list

anindya_bose — Tue, 31 Jan 2012 20:34:46 GMT

Yes, if you are using exception aggregation you need Ref Char for this.

Top N will only show Top values, other rows will be suppressed. But you can always right click on the query and Toggle condition. Once you deactivate the condition it will show you all the rows

In your case it would be 0CALDAY or 0CALMONTH whatever you want to see.

This will show you Maximum of Sales amount for a particular Calender Day or Calender Month based on your ref char selection.

Only thing is I guess it would show 120 for all the rows and I guess that makes sense because you can always check what was the max value against another sales value

Regards

Anindya

Re: Max value from a list

Former Member — Tue, 31 Jan 2012 21:52:03 GMT

Hi ,

Its not giving me the desired result. Can you tell me how to use the Max function in Formula. The function takes 2 parameters.

For instance MAX(Sales Amount, ?) , What would I include as the second parameter. Excel has a MAX function where you just give the range of cells and such as =MAX(A1: A10) and it would give you the Maximum value in that range. Doesnt BI have something simple like that? Thanks for your help.

Re: Max value from a list

Former Member — Tue, 31 Jan 2012 22:34:11 GMT

On the desired Keyfigure, right click --> properties --> Calculations --> Calculate Single Value as --> Select Maximum.

Hope this helps.

Thanks,

Naveen Rao Kattela

Re: Max value from a list

Former Member — Wed, 01 Feb 2012 15:15:21 GMT

Can you please tell me how the MAX() function works in the formula builder, it is supposed to take 2 parameters like this:

MAX(parameter 1;parameter 2). Thanks

Re: Max value from a list

Former Member — Wed, 01 Feb 2012 16:11:16 GMT

Hello,

 Can you please tell me how the MAX() function works in the formula builder, it is supposed to take 2 parameters like this:
MAX(parameter 1;parameter 2).

It doesn't work like excel - ranges are not supported.

As parameter you can use only objects that are visible in available operands (key figures, selections, formulas, formula variable) i.e. it basically compares 2 numbers.

To address your problem:

I suggest you select Calculate result as maximum - to see maximum in result row.

If you want to see maximum in column, then you create formula and use function Result/Overall Result on Monthly Sales. Function is found in data functions.

Ondrej

Re: Max value from a list

Former Member — Wed, 01 Feb 2012 16:32:36 GMT

Thanks for the info on MAX. I tried using Result and Overall Result but that didn't help. I have tried Exception Aggregation and that's not helping, I have tried creating a formula and then create a condition on that formula like TopN value 1 and that's not working . I mean how difficult is it in BW to pick a Max value from a list. In school I wrote Java and Python methods from scratch to find Max values in a list Basically you got to sort and do comparisons. I can't believe that something so simple and intuitive is so cumbersome in SAP. I am searching forums and looking for documentation for this but so far no luck. I appreciate all the answers and help I am getting from you guys. Thanks.

Re: Max value from a list

anshu_lilhori — Wed, 01 Feb 2012 16:53:40 GMT

Hi,

Try this make a new formula and put your amount keyfigure inside it then hit the aggregation tab and excption aggregation as MAX and reference characteristic as day.Now in result you will get 120 value.

Again make a new formula as SUMCT(F1) the formula which you created earlier.Now you will get 120 in every cell.

In case you dont want to display it in each cell then you can select calculate single value as hide.

Hope it helps and works.

Regards,

Re: Max value from a list

Former Member — Wed, 01 Feb 2012 17:04:43 GMT

Hello,

My bad sumct really does only SUM.

However it's even easier - just use calculate single value as maximum on KF Monthly Sales. I've just checked it.

Ondrej

Re: Max value from a list

Former Member — Sat, 04 Feb 2012 23:15:34 GMT

Thanks everyone for the input, I tried all the proposed suggestions but the optimal solution for me was to create a condition with TopN and Value = 1 and then in Character Assignment I selected "All Individual Chars. and Char.Combinations" and then selected the characteristics which helped give the maximum. Thanks again for all your help, It was a good exercise in learning how the different methods give a max value but each has its own nuances. For its Calculate Result as, Exception Aggregation with Maximum value and creating a condition with TopN and 1.