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
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
As always . Super !!. Thanks for Sharing
Thanks yogeeswara reddy for your encouraging note..
Great tips ! Such a powerful tool... 😎
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.
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.
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
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.
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?