Skip to Content
avatar image
Former Member

maximum of dimension value based on measure value in bo webi 4.1

Hi All,

I am using 4.1 Webi for the below requirement.

I'm running in to an issue while showing the band type with the highest amount within the relationship in the below example data set –

Here relationship #,relationship name, band type are defined as Dimensions and the amount is defined as a measure.

Eg -

Relationship # ---------------Relationship name----------Band type-----Amount
100----------------------------ABC --------------------------ES---------1000
100----------------------------ABC --------------------------IRE--------1001
100----------------------------ABC -------------------------PA---------0
111----------------------------BCD --------------------------HC---------2000
111----------------------------BCD --------------------------CH---------3000
111----------------------------BCD --------------------------EH---------1500
112----------------------------DEF --------------------------GF---------0
112----------------------------DEF --------------------------JH---------1200
112----------------------------DEF --------------------------YY---------1100

Output result should be shown as below -

Relationship #-----------------Relationship name---------band type
100-----------------------------ABC------------------------IRE
111-----------------------------BCD------------------------CH
112-----------------------------DEF------------------------JH

I need to get the Band Type for each relationship name where Amount is Maximum.

I tried by using the =max(band type) and this doesn't seems to be working on all scenario's

Tried using the rank and few other function but could not achieve the result as expected.

Can someone please help me on how to achieve this.

Add comment
10|10000 characters needed characters exceeded

  • Hi,

    Try:

    =Max([Amount) in ([Relationship Name];[Band Type])

    Thanks,

    Jothi

  • Former Member Jyothirmayee A

    Thank you Jothi for your reply !

    I tried as you have suggested, but it didn't help me. Here in the report I shouldn't show the amount in the report but i have to display the only dimension values corresponding to the maximum of the amount for each band type.

  • Get RSS Feed

2 Answers

  • Apr 10, 2017 at 05:34 PM

    follow these steps.

    Create V Amount=[Amount] (Dimension variable)

    Show/Hide=If([Amount]=Max([V Amount]) In ([Relationship # ])) Then "Show" Else "Hide"

    Apply filter on Show/Hide where equal to "Show"

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 10, 2017 at 06:09 PM

    Thank you Amit for your reply !

    Above mentioned logic doesn't seems to be working. It is not picking up the max value based on the dimension for all the records.

    Add comment
    10|10000 characters needed characters exceeded