cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
Here is an example of a widget script you can use to create a Waterfall Chart using the standard Column Chart widget.
First, create a column chart with multiple measures/values.
In the example below I have a starting price, and then two adjustments (these can be positive or negative).
Add the following code using the 'edit script' menu option in the widget:
widget.on('beforeviewloaded', function(widget,element,options){
    
  var chartOptions = element.options;
  chartOptions.chart.type = 'waterfall';
  chartOptions.legend.enabled = false;

  if (chartOptions.plotOptions.series.dataLabels.enabled == true){
    chartOptions.plotOptions.series.dataLabels.verticalAlign = 'top';
  }
  chartOptions.series.forEach(setSumSeries, chartOptions.series[0].data);
})

function setSumSeries(series, index) {
  if (series.name.startsWith("*")) {
    this[index].isSum = true;
  }
}
The column chart should now appear more like a waterfall chart.
To add summary columns (which total up everything to the left of where you place the summary column) add a new value to the chart and use the formula to enter a 0. (it does not matter what this value is, as it not used).
Then, rename the column so it starts with an asterisk e.g. * Final Price
Repeat for as many summary columns as you need, and drag them to the required position in the Values list.
Apply colour formatting as desired (I added conditional formatting for positive/negative adjustment values).
If you want to make additional changes to the chart, you can use the api reference for the waterfall chart here and set them using the chartOptions.<option> in the same way as in the example.
Rate this article:
Comments
tchauchat
7 - Data Storage
7 - Data Storage

Hi, Thanks for the script. 
Unfortunately it doesn't work for me 😕 

tchauchat_0-1685007791866.png

The only way to make it work is to have 1 category (date) and 1 value/measure.

I am on Version L2023.3.0.205, do you think it is related ?

steve
Sisense Team Member
Sisense Team Member

Hi,

Sorry about the delay ... as these posts were migrated from the old community by API, I did not get any notification of comments here!

It seems that this broke due to some changes to the internals of the series / data. I've modified the script a bit and it seems to work (tested on 2023.4)

Hope that helps! Let me know if you still have issues.

Steve

widget.on('beforeviewloaded', function(widget, element, options) {
var chartOptions = element.options;
chartOptions.chart.type = 'waterfall';
chartOptions.legend.enabled = false;
if (chartOptions.plotOptions.series.dataLabels.enabled == true) {
chartOptions.plotOptions.series.dataLabels.verticalAlign = 'top';
}
chartOptions = consolidateSeries(chartOptions)
delete chartOptions.xAxis.categories
chartOptions.xAxis.type = 'category'
delete chartOptions.yAxis[0].max
//console.log(chartOptions)
})

function consolidateSeries(options) {
options.series.forEach((thisSeries, index, arr) => {
options.series[0].data[index] = thisSeries.data[index]
options.series[0].data[index].name = thisSeries.name
if (thisSeries.name.startsWith("*")) {
options.series[0].data[index].isSum = true;
delete options.series[0].data[index].y
}
})

options.series[0].onlyMeasuresInSeries = false
options.series.splice(1, options.series.length - 1)
return options
}

 

 

Version history
Last update:
‎03-02-2023 09:05 AM
Updated by:
Contributors