Max value from a list

[Former Member]

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?

[Former Member]

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

Hope this helps.

Thanks,

Naveen Rao Kattela

[Former Member]

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

[Former Member]

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.

BR

Ondrej

[Former Member]

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.

[anshu_lilhori]

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,

AL

[Former Member]

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.

BR

Ondrej

[Former Member]

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.

[anindya_bose]

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

[Former Member]

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?

[anindya_bose]

Yes...

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

Regards

Anindya

[Former Member]

Can u give me the steps? plz

[anindya_bose]

Hi

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

[Former Member]

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.

[anindya_bose]

Hi

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

[Former Member]

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.