How to create a horizontal RSUM
How to create a horizontal RSUM
Introduction:
An easy way to create a horizontal RSUM in the Pivot table.
Step-by-Step Guide:
To create a Horizontal RSUM, we will use a script. But first of all, let's prepare our dashboard. Create a new dashboard based on Sample Ecommerce, add on it Months in Date as a ROWS, Age Range as Columns, and SUM as values with formula [Total Revenue].
It will look like:
[Alt text: "Screenshot of a data visualization tool showing Rows with 'Months in Date' and 'Age Range' in Columns. Values include 'SUM' and 'RSUM'. A formula, '[Total Revenue]', is highlighted in the Sample ECommerce section."]
In the next step, duplicate the SUM value and rename it RSUM.
Press 3 dots in the upper-right corner of the widget and select Edit Script.
[Alt text: A dropdown menu titled "Actions" is open, highlighting "Edit Script" in red. Options include changing data source and SQL queries.]
In the new window, copy and paste the following code:
const destinationFormulaTitle = 'RSUM';
prism.rSumConfig = prism.rSumConfig || {};
prism.rSumConfig[widget.oid] = [];
widget.on('beforequery', () => {
prism.rSumConfig[widget.oid] = [];
})
const target = {
type: ['value']
}
debugger;
widget.transformPivot(
target,
(metadata, cell) => {
const { colIndex, rowIndex, measure, type } = metadata;
const { title } = measure;
if (title === destinationFormulaTitle) {
const { content } = cell;
if (!prism.rSumConfig[widget.oid][rowIndex]) {
prism.rSumConfig[widget.oid][rowIndex] = [];
}
if (type.length === 1) {
const { rSum, currentValue } = getValue(content, prism.rSumConfig[widget.oid][rowIndex], colIndex);
prism.rSumConfig[widget.oid][rowIndex][colIndex] = currentValue;
cell.content = rSum.toFixed(2);
}
}
}
);
function getValue(currentValue, row, colIndex) {
currentValue = (!currentValue) ? 0 : parseFloat(currentValue.replaceAll(',', ''));
let rSum = 0;
for (let i=0; i < colIndex; i++) {
if (!isNaN(row[i])) {
rSum += row[i];
}
}
rSum += currentValue;
return {
currentValue, rSum
}
}
[Alt text: "A table shows monthly data from October to December 2013, categorized by age groups 0-18 to 65+. Key data highlighted in red boxes include RSUM values: 1129.09, 1709.80, 1896.26, 5527.58, 6085.98, 6275.74, and 6775.74. The layout is structured, with a focus on numerical analysis and comparison across age groups."]
Conclusion:
You can use the Horizontal and usual vertical RSUM for your Pivot for the best result for your business.
Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.
