Max or Sum a Row (Across Multiple Columns)

[Former Member]

Hi There I need to get the sum or maximum for a row across multiple columns.

Col1      Col2      Col3      Col4       Col5      Max
   6         2         4          3         4        6

For the above table I want to be able to get the maximum value for the row, across columns 1 to 5. So the formula (in Excel) would look like "=Max(Col1, Col2, Col3, Col4, Col5)" and I can't figure out how to do this in Webi.

Any help would be appreciated.

Regards,

Todd.

[Former Member · Accepted solution]

If you use the cross-tab all you have to do is choose the (max) option of the Sum filter. You'll get the max value for the row and column and you can delete the max summary row if you don't need.

[Former Member · Accepted solution]

Hi Jake thanks for the suggestion but unfortunately it doesn't work for the data in the above format. Your method would work if the data was trasposed accordingly (see below).

Column      Value
Col1         6
Col2         2
Col3         4
Col4         3
Col5         4

Max          6

However because I want to take the MAX accross multiple columns I cannot use the cross tab as it does not let me (take MAX of multiple columns, only the MAX for one column split out in the cross tab).

Any other suggestions? Thanks.

T.

[Former Member]

Hi,

You can use the if() statement to calculate max.

e.g. =if([Col1]>[Col2];[Col1];0)

use it many times

=if( (if([Col1]>[Col2];[Col1];0)) >[Col3] ; (if([Col1]>[Col2];[Col1];0)) ; [Col3])

......

This works fine...however looks a little bulky in length...

Thks& rgds,

Prabhat Kumar

09657720647

[Former Member]

Hi Prabhat, thanks for the solution. I did think of this however I have 48 columns and I am not prepared to put all that into code. Way too messy.

T.

[Former Member]

I also experienced a similar type of problem before...........

I am sorry but thing is, i also solved that problem using a formula like provided before..........

[Former Member]

If your Dimensions in report is fixed you can use this below. Yes I know this would not be easy to implement DATABASE side functions as this might affect your Adhocing capability.e.g.

for Oracle Greatest()

e.g. select greatest(44, 22, 49, 8, 102) from dual;

102

You can add another Query at your convenience, make use of a dummy object and Edit SQL to add this function for quick report side solution.