cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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.
Version history
Last update:
‎03-02-2023 08:33 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: