Dynamic Buckets
*This plugin is officially supported by Sisense. You can find the latest version of this plugin on the Sisense Add-Ons page.
Introduction
This article will explain how to aggregate a column chart results into buckets
Business Case
Aggregating results into buckets provides the user the ability to dynamically or manually segment the results population into groups - while still keeping the filtering abilities.
Example
To analyze our revenue per customer, we can use the regular column chart and present revenue per customer on each column. However when having a lot of customers, its a bit hard to gain business value out of it
By using the plugin, we can segment our customers by their revenue. The formatting for the bucket labels is set in the formatting options of the first value. In the example below we can see that 76 of our customers have revenue less than $24,00 and the total spending of these customers is ~$600,000.
It is also possible to manually specify the min and max values for each bucket. For our example above, most of the customers fall withing the first bucket. If we wanted to manually specify ranges from 0 - 10k, 10k - 20k, 20k - 40k, and 40k+, then we can pass those thresholds to the plugin.
Steps
Step 1 - Download and extract the enclosed folder in the plugins folder:
C:\Program Files\Sisense\PrismWeb\plugins\dynamicBuckets, if the "plugins" folder is not there, please create it
Step 2 - Creating the column chart widget
-
Choose only one category
-
In the first value define the measure by which the bucket will be calculated, for example sum(revenue)
-
In the second measure define the aggregation per bucket, for example count(client) / sum(revenue) / avg(revenue)
-
You can define additional measures
-
Click apply to save the widget
Step 3 - Script Options
Edit the widget, and edit the widget script. There are 2 type of bucket ranges you can define
-
Manual ranges, allows to manually define the buckets ranges. Define the options based on the below sample.
<type> , set it as 'manual'
<separator>, define the separator between the ranges, for example ‘ - ’
<clickBucketOption>, define the click bucket option: rather to set dashboard filters or to export to CSV file. Optional values: 1 or 2.
<ranges>, list of buckets to use
<bucket>, provide an object with properties for name, color, min, and max
Example: to define the below ranges:
1. less than 0
2. greater equal 0 less than 15
3. greater equal 15 less than 30
4. greater equal 30
use this script:
var options = {
type: "manual", // (required) Can be 'auto' or 'manual'
separator: " - ", // (optional)
clickBucketOption: 1, // (optional) Can be 1 (default) or 2. 1- sets column categories as filters,
// 2- exports column categories to CSV file.
ranges: [ // Manually define the buckets you want to use
// This will group any values less than 0
{
name: "< $0", // Label for the bucket
color: "#3471AD", // Color can be text or a hex code
min: null, // The lower limit for the bucket, put null for no lower limit
max: 0 // The upper limit for the bucket, put null for no upper limit
},
// This will group any values between 1 & 15
{
name: "$0 - $15",
color: "#F66500",
min: 0,
max: 15
},
// This will group any values between 15 & 30
{
name: "$15 - $30",
color: "#FFC400",
min: 15,
max: 30
},
// This will group any values greater than 30
{
name: "> $30",
color: "#42BC39",
min: 30,
max: null
}
]
};
-
Dynamic ranges, allows to define the number of buckets - ranges will be calculated automatically by the Min & Max of the widget values.
< type> , set it as 'auto'.
<separator>, define the separator between the ranges, for example ‘-’, ‘to’.
<clickBucketOption>, define the click bucket option: rather to set dashboard filters or to export to CSV file. Optional values: 1 or 2.
<numberOfBuckets>, define the number of buckets.
Example: to define 5 buckets
use this script:
var options = {
type: "auto", // (required) Can be 'auto' or 'manual'
separator: " - ", // (optional)
clickBucketOption: 1, // (optional) Can be 1 (default) or 2. 1- sets column categories as dashboard filters
// 2- exports column categories to CSV file.
numberOfBuckets: 5 // (required) How many buckets should we calculate
};
Step 4 - Applying the JS
Place the below code right after defining the options, to create the buckets:
prism.dynamicBuckets(widget, options )
Step 5 - Save the script, refresh the widget and click “apply”
Notes
- This script has been updated as of July 17th 2015, July 23, 2015 August 12, 2015 Oct 29, 2015
- Clicking on a column in the chart selects the specific members that make up the bucket. So in our example above, clicking on the column for Customers > $40k, it will set a filter on the customer dimension and set the selection to each customer that falls within this bucket.
- Older versions of this plugin caused issues with PDF printing when using plugins on a dashboard. Please update to the latest version to resolve the issue
- On large amount of rows, the option to set dashboard filters on click can cause slowness. On this case, use the option to export to CSV file
26 March 2018 - the plugin was updated. Added filtering functionality for "highlight" and "slice/filter" modes. Fixed widget selection.
17 October 2018 - The plugin was updated for 7.2 compatibility. Issues with selection not working properly when filtering on more than 2 charts were resolved. Issues with '0' value being excluded by default have been resolved.
-
Great feature, however I can not get the filtering ability to work. When you click on a bar a page filter should appear to filter for that group.( i.e in your example if you click on the 25k-50k bar a page filter should appear for Revenue between 25k and 50k). However, what I have happen is a general page filter that does not do anything ( i.e in your example it would be a filter for Include All Clients) . How do I get the filtering to work?
-
HI Alon
Thanks for the update. Is it possible to have the filter show the revenue instead. For example instead of clicking on a column in the chart selects the specific members that make up the bucket have it show you the range it is in. So in our example above, clicking on the column for Customers > $40k, it will set a filter on the revnue dimension and set the selection to each revenue amount that falls within this bucket.
Thanks;
Cassandra
-
Hi Cassandra,
The plugin will filter the field you have in the categories.
Usually revenue is not a dimension but an aggregated value that calculated across many rows.
If in your case a revenue is an attribute of a client, place it as a category.
Hope i understood your question correctly,
Alon
-
Hi Alon
What we were hoping for is that when you click on a bucket you would get a range filter based on the revenue. The issue we are running into is that, lets say you have 1000+ clients with a revenue of $10k - $20k. When you click on that bucket the filter shows all 1000+ clients individually which takes a long time to load.
Cassandra
-
Hi Cassandra,
Unfortunately dashboard filters based on measures are not yet available, i think its a great idea, please post it in our Feature Request Forum at: https://support.sisense.com/forums/21642234-Feature-Requests. Our team frequently visits this forum to consider functionality changes and feature requests from our online community.
In addition, I have another idea that you might find useful. In version 5.8 (our latest version) by using a compound filter, you can create separate chart / pivot for you customer's groups.
- Example attached -
Alon
Buckets.dash -
When exporting a dashboard to pdf that has a widget with this plugin I get an error. Exporting an individual widget to png works fine. Any workaround ideas?
- Error: undefined is not a constructor (evaluating 'Math.log10(unroundedTickSize)') at calculateBucketsAuto (http://localhost:8081/plugins/reporting.js:493:31) at http://localhost:8081/plugins/reporting.js:348:35 at http://localhost:8081/js/common.js?g=goeoAodAAfi:57:1851 at forEach ([native code]) at trigger (http://localhost:8081/js/common.js?g=goeoAodAAfi:57:1831) at v (http://localhost:8081/js/app-reporting.js?g=goeoAodAAfi:123:9866) at http://localhost:8081/js/app-reporting.js?g=goeoAodAAfi:123:11481 at u (http://localhost:8081/js/common.js?g=goeoAodAAfi:32:29623) at http://localhost:8081/js/common.js?g=goeoAodAAfi:32:29795 at $eval (http://localhost:8081/js/common.js?g=goeoAodAAfi:33:5603) at $digest (http://localhost:8081/js/common.js?g=goeoAodAAfi:33:4039) at $apply (http://localhost:8081/js/common.js?g=goeoAodAAfi:33:5890) at a (http://localhost:8081/js/common.js?g=goeoAodAAfi:32:13986) at m (http://localhost:8081/js/common.js?g=goeoAodAAfi:32:15576) at onload (http://localhost:8081/js/common.js?g=goeoAodAAfi:32:16116)
-
Instead of seeing each dynamic bucket's value range, you may want to see the bucket as a percentage.
Here's a widget script you can use for 10 buckets:
const percentiles = ['10%', '20%', '30%', '40%', '50%', '60%', '70%', '80%', '90%','100%'];
widget.on('domready', () => {
var labels = $('g.highcharts-xaxis-labels text', element);
labels.each((i, v)=> {
$(v).text(percentiles[i]);
})
}); -
Hi,
Not sure if it is me but I am seeing a discrepancy between auto bucket generation and manual bucket generation.
I have 10 buckets between 0 and 100%, 0 to 10, 10 to 20 ...... and so on.
The discrepancy occurs at the bucket boundaries. On the auto generate the final bucket 90-100 excludes the value 100.
The manual bucket generation gives me what I need for now.
Please sign in to leave a comment.
Comments
15 comments