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.

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]);
})
});

Cassandra July 03, 2015 12:48

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?

Alon Ahronberg July 22, 2015 12:59

Hi Cassandra,

The plugin was updated to allow filtering from a bucket. Please check the revised version:

Replace the previous files with the updated ones
Follow the above instructions

Best

Matt July 28, 2015 15:24

Does the plugin allow for secondary categories on the X-axis (for example, quarters in date)? I tried adding a second category, but the widget broke and the browser page became unresponsive.

Alon Ahronberg July 29, 2015 19:11

Hi Matt,

Plugin supports 1 category.

Cassandra August 04, 2015 16:37

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

Alon Ahronberg August 05, 2015 15:39

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

Cassandra August 05, 2015 16:05

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.

Alon Ahronberg August 06, 2015 08:27

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 -

Buckets.dash

Scott Vickers August 05, 2016 04:00

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)

Tim Coppola December 12, 2016 14:12

Filtering still doesn't work.

Artem Yevtushenko March 25, 2019 19:52 (Edited March 26, 2019 18:41)

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:

Simon Tuson April 15, 2019 17:53

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.

Jordy Finkers April 16, 2019 06:17

Hi Simon,

We had a similar kind of issue. After some time we got a test version from support for this plugin with a fix.

That one seems to work. Support should be able to provide you a version.

Dynamic Buckets

Step 1 - Download and extract the enclosed folder in the plugins folder:

Step 2 - Creating the column chart widget

Step 4 - Applying the JS

Comments

Didn't find what you were looking for?