Automatically Sort Values Into Ranges Of Known Size

If you know the size your range/bucket will increment by, this quick method will help you automatically sort your values into their respective range.

This is useful when you cannot create or maintain ranges using a CASE statement.

In the below example, I will put pricing data into ranges that increment by 100000.

Eg. the price '650000' will be automatically placed into the '600000–700000' range.

Solution

1. Your table should contain the field you want to count (in this example, an 'id' field) and the value ('price').

2. Add a new custom field to your table, change the name to 'div' and change its type to integer.

3. In this field, edit the expression to the following:

DIV( [value] , <your increment> )

For this example, it would be:

DIV( [price] , 100000 )

4. Add a new custom field to your table, change the name to '<insert your value>_group' (in my case, 'price_group') and change its type to text.

5. For this field, edit the expression to be:

TOSTRING( [div] * <your increment> ) + '–' + TOSTRING( ( [div] + 1 ) * <your increment> )

For this example, it would be:

TOSTRING( [div] * 100000 ) + '–' + TOSTRING( ( [div] + 1 ) * 100000 )

The values will be automatically categorised under their respective groups, so each row of my pricing data will belong to a price_group.

6. In the dashboard, create a column chart.

Place the div field in as a category, followed by the your grouping field (in my case, price_group).

Add the field you want to count in as a value.

5. Edit the following javascript code into the widget’s script and the div field label will disappear from the top of the chart.

widget.on('processresult', function(widget, ev){

     debugger

     ev.result.series[0].data.forEach(function(d){

          console.log(d.selectionData);

          if(d.selectionData){

               delete d.selectionData[0]

          }

     })

  delete ev.result.xAxis.plotBands

     for(var i=ev.result.xAxis.categories.length-1;i>=0;i--){

          if(ev.result.xAxis.categories[i] == " "){

               ev.result.xAxis.categories.splice(i,0);

          }

     }

})

Your values will now be automatically sorted into ranges/buckets.