This discussion is archived
1 Reply Latest reply: Apr 16, 2012 12:53 AM by MuRam RSS

SUM over Max of rows

728159 Newbie
Currently Being Moderated
Hi all,
I am facing this problem, where in i have to find the Max of a set of rows based on paritition. Then sum all those max values and show it in the OBIEE. Is there any way we can do that in Criteria expressions of OBIEE Presentation Layer? Basically i am trying to find the header total at line level. Therefore for a given set of records the total amount will always be the same.


For e.g.
Header id Amount
1234 -> 100
1234 -> 100 MAX value - 100 Total = 100 + 200 = 300
1234 -> 100

1235 -> 200
1235 -> 200 MAX value - 200
1235 -> 200



This is how i want to show. Is it possible?


Thanks,
Upendra
  • 1. Re: SUM over Max of rows
    MuRam Expert
    Currently Being Moderated
    Hi,

    Yes tried this once.
    First Solution:
    Use three columns
    1. Dimension
    2. Measure column
    3. Rcount(Measure_column)

    Third column should have the column formula as-
    case when Rcount(Measure_column)=2 then 'Max'||MAX(Measure_column) end

    Second Solution:
    Use the column formula in the second measure column itself as
    cast(Measure_column as char)||case when Rcount(Measure_column)=2 then 'Max'||MAX(Measure_column) end

    NOTE- 2 above refers the position where the message has to be displayed.

    Award if this is helpful.

    Regards
    MuRam

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points