asked on June 19, 2025
Hi,
I want to use calculated fields in my reports
1. I want to calculate variance of deposits e.g. Current Year vs Prior or current quarter vs prior quarter or over product a product b In fact column A and Column B may have different hierarchies such as year, date, product branch etc.
2. I am show list of deposits by branch and products and I want show % of deposits with respect to grand total shown at the bottom of the column
Regards,
Gokul
⋅ June 20, 2025
Hello, Gokul!
Thank you for writing to us.
For displaying variances between different columns, we recommend using the differenceofrow or differenceofcolumn aggregations. They calculate. In case of differenceofrow cells show the difference between two adjacent cells of the same level from left to right, with differenceofcolumn the difference is calculated between adjacent cells from top to bottom. You can find usage examples for these cases below:
To show the percentage of grand total, use the percentofcolumn, which calculates the percentage of each value relative to each separate total of the column at the bottom: https://jsfiddle.net/flexmonster/2y5m63uf/
Also, Flexmonster has a percent aggregation, showing percentages relative to the grand total of all values at the bottom right: https://jsfiddle.net/flexmonster/k3b10qjx/
Please let us know if these aggregations are what you were looking for.
Best Regards,
Maksym
⋅ 5 days ago
Hi Maksym,
The variance functionality we want is as shown in the attached Excel sheet.
The example given above doesn't work exactly as we need.
Regards,
Gokul
⋅ 4 days ago
Hello, Gokul!
Thank you for sharing more detailed examples with us.
You can create a similar layout with Flexmonster using the differenceofrow and %differenceofrow aggregations: https://jsfiddle.net/flexmonster/uw09b7ma/
With these aggregations, each cell shows either the difference or the percentage difference between two adjacent cells of the same level from left to right. The first column of this aggregation is always empty, as there are no adjacent values to calculate the difference.
Please let us know if this would work for you.
Best Regards,
Maksym
⋅ 4 days ago
Hi Maksym,
Thanks. But I want to hide the empty columns. How do I handle this?
Screenshot attached.
Then report will look perfect and also I want to rename the measure as variance and variance % instead of differenceofrow and differenceofrow%
Regards,
Gokul
⋅ 3 days ago
Hello, Gokul!
Thank you for your reply.
To hide the empty columns, you can set their width to zero. This can be defined in the report by setting the TableSizesObject:
"tableSizes": {
"columns": [
{
"tuple": ["year.[2024]"],
"measure": {
"uniqueName": "Deposits",
"aggregation": "differenceofrow"
},
"width": 0
},
{
"tuple": ["year.[2024]"],
"measure": {
"uniqueName": "Deposits",
"aggregation": "%differenceofrow"
},
"width": 0
}
]
}
However, this would cause the numeric sheet header mismatch, so you can hide them entirely by setting the showHeaders option to false. The measures can be renamed by setting the caption and disabling the aggregation captions with the showAggregationLabels option.
We have modified the previous JSFiddle example to include these changes: https://jsfiddle.net/flexmonster/u0scgmnr/
Looking forward to hearing your feedback.
Best Regards,
Maksym
⋅ 3 days ago
Hi Maksym,
Thanks for providing the solution to hide the empty columns. But I want to handle this dynamically as I can change filter for year and change it to any other years to compare. Or for account type variance I can select any other filters.
Please advise how to handle tableSizes structure to dynamically update tuple sections.
Regards,
Gokul
⋅ 2 days ago
Hello, Gokul!
There is no way of dynamically setting the table sizes so that the empty columns within the differenceofrow and %differenceofrow aggregations would be hidden. As a tradeoff, you either keep those empty columns or avoid filtering and sorting to make them hidden, making a more static report.
Please let us know if more questions arise.
Best Regards,
Maksym
