Max value from a list

[#1]

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?

[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.