Top 9 and 'others' in Infoview

system · June 7, 2010, 3:42pm

Hi All,

I want to calculate and show in CHART, Top 9 brands and rest should come under ‘Others’ which is not actually at the db level. So, I need to acheive this @ the universe/report level.

ex, in bar graph
Y-Axis->Revenue
X-Axis->b1,b2 b3…b9,Others

Is there any way we can acheive this in Infoview or in universe.?

Please help me.

regards,
Prasath.

nprasath2007 (BOB member since 2010-06-07)

system · June 8, 2010, 2:56am

hi!

here’s a solution for webi https://bobj-board.org/t/156066

search is your friend!

R.A. (BOB member since 2008-10-28)

system · June 8, 2010, 10:57am

Hi,
I tried using the if(rank())>4;“Others”;[Brand]) , it works in tha table but it doesn’t work in the bar chart.

Any other effective solutions please.

regards,
Prasath.

nprasath2007 (BOB member since 2010-06-07)

system · June 9, 2010, 12:51am

wow! stubborn little child!

anyways… you did not search the forum that well… here’s the answer!

=If(IsError(If(Rank(<measure>;<dimension>) < 3;<dimension>;"Other") In(<dimension>));"OTHER";If(Rank(<measure>;<dimension>) < 3;<dimension>;"Other") In(<dimension>))

you can find the answer here: https://bobj-board.org/t/139201

READ THE TOPIC THOROUGHLY.

R.A. (BOB member since 2008-10-28)

system · June 9, 2010, 11:21am

Thanks for your reply.

But, with that formula…

=If(IsError(If(Rank(;) < 3;;“Other”) In());“OTHER”;If(Rank(;) < 3;;“Other”) In())

I tried it, so the first part in the IF condition always gives you the ‘Multivalue Error#’, So, the IsError() function doesn’t fail.So, the ‘OTHER’ value is geting displayed. with just ONE BAR

Bus what about the rest of the vaules in the dimension which is less than the ranking value…?

I want to show…b1,b2…b9,Other in BAR CHART.

Even I tried, =If(If(Rank([Sales revenue];[City])>4;“Others”;[City])=[City];[Sales revenue];Max(If(If(Rank([Sales revenue];[City])>4;“Others”;[City])=[City];[Sales revenue];RunningSum([Sales revenue]))) ForAll ([City]))

The above code works and shows you the ‘OTHER’ total but we will see the Xaxis with

b1,b1,b3,b4,…b9,b10.

the condition here is where rank>4 is ‘OTHERS’. So, for b4 to b10 it will show you the ‘Others’ Total in the y axis ‘bar’ but the X-axis still displays b4…b10

any suggesions please…?

Also, I have gone thro’ the TOPIC THROUGHLY…but no use.

regards,
P

nprasath2007 (BOB member since 2010-06-07)

system · June 9, 2010, 12:04pm

hhhmmm… it’s working fine in my testing… just to check, did you make your rank forumula a detail? if not, force it to be a detail not a dimension.

R.A. (BOB member since 2008-10-28)

system · June 9, 2010, 3:22pm

Hi R.A,

yeah I am tried making it ‘DETAIL’, but still its showing just the ‘OTHER’ in Xaxis and the correspoing value in the yaxis.

Any other suggesions…?

Thanks,
P

nprasath2007 (BOB member since 2010-06-07)

system · June 10, 2010, 8:48am

Guys…?

Help me out pleazzzzzzzzzzzz…

any other suggesions…?

regards,
P

nprasath2007 (BOB member since 2010-06-07)

system · June 10, 2010, 9:43am

Hi I have tried this using Island Resorts Marketing.

Ranking based service and revenue. Created a variable called TOP6 - =If([Ranking]>5;“others”;[Service])In([Service])

Then created Pie chart using : TOP6, Revenue.

Kon Bobj (BOB member since 2005-06-06)

system · June 10, 2010, 10:13am

Hi,

Still I am geting the CONTEXT# or MULTIVALUE# error.

If it is Top6,Revenue and Service then we will see something in the PIE/Bar chart. but the axis shows like Ser1/Others,Serv2/Serv2…etc.

Any other pls…?

Thanks,
Prasath.

nprasath2007 (BOB member since 2010-06-07)

system · June 11, 2010, 10:23am

?..???

Guys common any creative ideas…?

nprasath2007 (BOB member since 2010-06-07)