⏱️ 4 minute read
Here are a few ready-to-use widget scripts to reorder or remove elements in your widget.
Replace the variables highlighted in bold with the appropriate values for your use case.
- Reorder date columns from newer to older
- Reorder the legend in 'Break by' charts
- Reorder bars or columns in charts
- Reorder stacked column sections from largest to smallest
- Group smallest values in a column/bar called "Others"
Reorder date columns from newer to older
widget.on("buildquery", function(se,ev) {
ev.query.metadata[1].jaql["sort"]="desc";
})Reorder the legend in 'Break by' charts
This will also reorder the columns themselves.
If you simply want to display the legend in reverse order, you can use this script:
widget.on("processresult",(se,ev)=>{ev.result.legend.reversed=true});If you want to display the legend in a custom order, you can use the below script.
const breakby = ['category name 2', 'category name 3', 'category name 1']; //Specify the category names
widget.on('processresult', function(se, ev) {
ev.result.series.sort(function(a, b) {
if (breakby.indexOf(a.name) < breakby.indexOf(b.name)) {
return -1;
} else if (breakby.indexOf(a.name) > breakby.indexOf(b.name)) {
return 1;
}
return 0;
});
ev.result.legend.reversed = false;
});
Reorder bars or columns in charts
Replace the "category name " variables by the names of your bar categories.
var categories= ['category name 2', 'category name 3', 'category name 1']; //Specify the category names
widget.on('queryend',function(se,ev){
ev.rawResult.values.sort(function(a, b){
var aIndex = categories.indexOf(a[0].data);
var bIndex = categories.indexOf(b[0].data);
if (aIndex < bIndex)
return -1;
if (aIndex > bIndex)
return 1;
return 0;
})
})
Reorder stacked column sections from largest to smallest
This script removes the legend.
(function() {
var initialized = false;
widget.on('ready', function(se, ev) {
var $el = $(element);
if ($el.find('.granularity-toggle').length === 0) {
var toggleHtml = `
<div class="granularity-toggle" style="text-align:center; margin-bottom:10px;">
<button class="granularity-btn" data-grain="years">Years</button>
<button class="granularity-btn" data-grain="quarters">Quarters</button>
<button class="granularity-btn" data-grain="months">Months</button>
</div>
`;
$el.prepend(toggleHtml);
$el.find('.granularity-btn').css({
'margin': '0 5px',
'padding': '5px 10px',
'border': '1px solid #ccc',
'border-radius': '4px',
'cursor': 'pointer',
'background': '#F8F8F8'
});
$el.find('.granularity-btn').off('click').on('click', function() {
var grain = $(this).data('grain');
updateGranularity(grain);
setActiveButton(grain);
});
}
function setActiveButton(grain) {
$el.find('.granularity-btn').css({ background: '#F8F8F8' });
$el.find(`.granularity-btn[data-grain="${grain}"]`).css({ background: '#D1E7FD' });
}
function updateGranularity(grain) {
var panels = widget.metadata.panels;
var categoriesPanel = panels.find(function(p) {
return p.name === "categories";
});
if (!categoriesPanel || !categoriesPanel.items.length) return;
var dateDim = categoriesPanel.items[0].jaql;
if (!dateDim) return;
if (grain === "years") {
dateDim.level = "years";
} else if (grain === "quarters") {
dateDim.level = "quarters";
} else if (grain === "months") {
dateDim.level = "months";
}
widget.refresh();
}
});
})();//Order columns largest to smallest
widget.on('processresult', function(widget, result) {
const originalSeries = result.result.series.map((series) => ({
name: series.name,
data: series.data,
color: series.color
}));
const numColumns = result.result.xAxis.categories.length;
const columnData = [];
for (let colIdx = 0; colIdx < numColumns; colIdx++) {
const sorted = originalSeries.map((series) => ({
name: series.name,
value: series.data[colIdx].y,
color: series.color
})).sort((a, b) => b.value - a.value);
columnData.push(sorted);
}
const newSeries = [];
for (let rank = 0; rank < originalSeries.length; rank++) {
const seriesData = [];
for (let colIdx = 0; colIdx < numColumns; colIdx++) {
const item = columnData[colIdx][rank];
if (item) {
seriesData.push({
y: item.value,
color: item.color,
originalName: item.name
});
} else {
seriesData.push({ y: 0, originalName: '' });
}
}
newSeries.push({
name: '',
data: seriesData,
stack: 'column'
});
}
result.result.tooltip = {
shared: false,
formatter: function() {
// Format this.point.y as USD currency
const formattedValue = this.point.y.toLocaleString('en-US', {
style: 'currency',
currency: 'USD',
minimumFractionDigits: 0,
maximumFractionDigits: 0
});
return (this.point.originalName || '') + ': ' + formattedValue;
}
};
result.result.legend = { enabled: false };// Hide only Sisense's cartesian tooltip for THIS widget only
var $widgetElement = $(element);
var styleId = 'sisense-widget-tooltip-' + widget.oid;
if (!document.getElementById(styleId)) {
var style = document.createElement('style');
style.id = styleId;
var widgetSelector = '[data-widget-oid="' + widget.oid + '"]';
style.textContent = widgetSelector + ' .cartesian-tooltip-values, ' + widgetSelector + ' .cartesian-tooltip { display: none !important; visibility: hidden !important; }';
document.head.appendChild(style);
}result.result.series = newSeries.reverse();
});
Group smallest values in a column/bar called "Others"
Pie charts have an option to display top n slices and group the rest of the slices to a single slice called 'Others'. This script does the same for Column/Bar charts.
widget.on('processresult', function(widget, result) {
//specify the number of columns to keep, the rest will aggregate in "Others"
var topN = 5;
var othersLabel = 'Others';
var newValues = [];
var othersValue = 0;var categories = result.result.series[0].data.map((point, index) => {
return {
name: result.result.xAxis.categories[index],
y: point.y,
color: point.color
};
});categories.sort((a, b) => b.y - a.y);
var topItems = categories.slice(0, topN);
var othersItems = categories.slice(topN);othersItems.forEach(item => {
othersValue += item.y;
});topItems.sort((a, b) => a.name.localeCompare(b.name));
if (othersValue > 0) {
topItems.push({
name: othersLabel,
y: othersValue,
color: '#cccccc'
});
}result.result.series[0].data = topItems;
result.result.xAxis.categories = topItems.map(item => item.name);
});
Comments
0 comments
Please sign in to leave a comment.