Skip to Content
Author's profile photo Former Member

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

11-12-2013 11-55-18 AM.jpg

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..

11-12-2013 12-00-40 PM.jpg

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..

11-12-2013 1-14-38 PM.jpg

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

11-12-2013 1-26-19 PM.jpg

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

Hope you enjoyed this blog… Please share your thoughts and comments..

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

Assigned tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      As always . Super !!. Thanks for Sharing

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks yogeeswara reddy for your encouraging note..

      Author's profile photo WILLIAM MARCY
      WILLIAM MARCY

      Great tips ! Such a powerful tool... 😎

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks William..

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      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..

      Author's profile photo WILLIAM MARCY
      WILLIAM MARCY

      Great Webi trick !

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

      William

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      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..

      Author's profile photo Tom Munson
      Tom Munson

      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.

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      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..

      Author's profile photo Former Member
      Former Member

      This does not work well with negative values.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

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

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Former Member
      Former Member

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