# Webi tricks : Selecting Top N customers by Sales Percentage using a Slider

Recently stumbled upon a discussion post which describes a very nice use case for customer interaction using a slider type of input control.

So basically the slider will have a range of 0-100 % and based on the user slider selection the report the report will only show up the result for the top customers contributing the selected percentage of revenue..

So for example if I select the slide to 100% it will show all the customer and if the slider is selected to 0% it will not show any customer and if the user select 70% the report will show up the top customers who contribute for the 70% of the revenue.

Here are some screenshot to show what I mean …

This shows 1.00 which mean 100% and report shows all the stores for it

Now If I select .75 (75%) the below screen shows only the top stores that cumulatively contribute 75% of the revenue shows up in the screen below..

Now Here are the steps to achieve this…

I have create a regular dimension type variable called a  “slider value selection” to store the value selected by the slider. It just a plan dimension with any number as out put..

Now I am creating the slider on top of that dimension with a filter operator as equal to. I am using the range of slider as 0 to 1 with a interval of .01 and default of 1 to show all stores by default. So this will store the user selected slider value to the already created dimension..

Now the next step is setting up the cumulative measure for Sales Revenue which is a very simple variable called “Running Sum Rev” = RunningSum([Sales revenue])

and another variable for total revenue called “total Rev” =Sum([Sales revenue]) In Report

and on top of that I am creating another variable for cumulative percentage as “percentage” =[Running sum rev]/NoFilter( [total Rev])

Please note we want the total revenue to neglect any filter we might apply on the table so I am using a Nofilter() function..

created a Measure:Sales rank

=Rank([Sales revenue];Top)

created another measure : Sales rank threshhold

=ToNumber(  Max([Sales rank] Where ([Percentage] <= ToNumber( [Slider Value selection])) ) In Block  ; “0” )

created another measure : Slide Filter on Rank

= If NoFilter(  [Sales rank] ) < NoFilter(  [Sales rank threshhold] ) Then 1 Else 0

Now created a block level filter

Slide Filter on Rank = 1

Once this is done… you are good to go …you now have a webi report with advanced user interaction ability which was traditionally only a possible with Dashboard still with much more efforts

Also lastly my heartiest thanks to for coming up with a nice use case to solve

### Assigned Tags

You must be Logged on to comment or reply to a post.

As always . Super !!. Thanks for Sharing

Former Member
Blog Post Author

Thanks yogeeswara reddy for your encouraging note..

Great tips ! Such a powerful tool... ðŸ˜Ž

Former Member
Blog Post Author

Thanks William..

Hey Durgamadhad, can you share how you made the variable called a  "slider value selection" to store the value selected by the slider. I have the similar issue to resolve. I think wonderful article.

Former Member
Blog Post Author

That is the easy part just create a dimension variable with formula defines as any number and assigned the variable to the slider input control..

Great Webi trick !

You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

William

OK, I am having trouble at this step

created another measure : Sales rank threshhold

=ToNumber(  Max([Sales rank] Where ([Percentage] <= ToNumber( [Slider Value selection])) ) In Block  ; "0" )

I am unable to create the Sales rank threshold variable. Get a syntax error.

Former Member
Blog Post Author

Please make sure to create the [Sales rank] , [Percentage] , [Slider Value selection] variables as measures for this formula to work..

I'm getting a syntax error too.

You are passing two arguments to the ToNumber function, but it only allows one. The first argument is the Max In Block function and the second argument is "0". What are you trying to accomplish here?

Also, the text of the article states that [Slider value selection] should be a dimension and your comment above says it should be a measure. I get the syntax error either way.

I'm using BO 4.0 SP4.

i removed the double quote " from 0  then it worked for me

Thanks for sharing this webi trick.

I followed the steps to create slider as described in this blog, but "slider filter on rank" doesn't produce correct output. it randomly filters rows that doesn't make sense. I tested this with BO 4.0 and 4.1 and they both produce the same result. Please let me know if there any missing steps in your blog. Thank you in advance.

MKB

Former Member
Blog Post Author

Most likely there is one or more objects which are not defined as measures which can cause this type of issue. Please make sure to make all the objects defined correctly as I mentioned in the blog for this approach to work..

This does not work well with negative values.

Former Member
Blog Post Author

Could you explain what exactly you are trying to do with negative values?

"Running Sum Rev",total rev and percentage are dimensions or measures? and what is input slider,count store and total store dimensions??

How to get Count of Supplier Store made 90% of the Sales?