on 07-22-2009 2:39 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.