Calculating for "All Others"

[Former Member]

Hi everyone.

I have an interesting requirement I've been working through trying to figure out for a while now.

The requirement has a set number of tables based on 6 dimensions, some have ranking of the top 10 plus input controls to further filter by.

They want to sum the table columns results plus add an "All Others" summation of the rest of the values outside the ranked filter.

I've done a formulas like: sum(nofilter(<balance>;drill)) - sum(<balance>) etc.

I really like to ask for a hand of the requirement with the formula or if another path to consider as well.

ps.

BOBJ 4.1sp6

report off a bex query

Thanks all.

[Former Member]

Hi Jayson,

What you can do is create a dimension that calculates the Rank using Rank() function

[RankDimension]=Rank([Measure])

and then you can use this dimension to group to All Others

something like below

=If [RankDimension]<=10 Then [DimensiontoShow] Else "All Others"

Hope this helps.

Regards

Niraj

[Former Member]

Hi Niraj.

that's good, I'm understanding it, I'm struggling with the application of the formula syntax.
  it's showing 1 rank in the table for all the values.  Which is weird.

I have the table Category

|Category Column | measure 1 | measure 2 | Total Revenue |

|                             |                   |                   |                         |

Dimension variable [RankCategory]

=Rank([TotalRevenue];([Category])

Dimension Variable [ViewCategory]

=if [RankCategory] <= 10 then [Category] else "All Other"

I test the [RankCategory] in the table to see the rank values beside the [Category] and they are all 1.

adding [ViewCategory] beside (or replace) [Category just repeats the values.  Likely from Rank are all 1.

Changing the formula to "<= 0" it displays as expected.  So I must be not getting the Rank() function working properly yet.

Let me know your thoughts and thanks!

[Former Member]

Hi,

Can you have a look on this publication that may help you: http://scn.sap.com/docs/DOC-72269

Didier