How to Achieve a Weighted Average Rate Cross-Tab Report
My users are financial people in financial institutions, where sometimes a table of numbers is easier to analyze than a chart.
I'm trying to build a cross-tab report showing weighted average yields over time for various balance sheet assets (investments, loans), with the assets field having drilldown capability (this drilldown is critical).
The order of the weighted average calculation is critical here. Importantly, I can't sum the % yields for the entire drilldown hierarchy. For example, two asset products each with a yield of 1% does not equal a weighted average yield of 2%.
Rather, I need to sum both the numerator (an income or expense $) and denominator (a balance $), then divide the numerator by the denominator to show the weighted average.
The first attached screenshot shows this working just fine in flat file format.
All I need is to pivot the Fiscal Year field so it shows up as columns. However, Yellowfin's report builder requires that I aggregate the metric - which, in this case, is a calculated field that sums the numerator and denominator as noted above (second screenshot shows the calculation).
When I add a SUM aggregation on the metric, the "aggregate function calls cannot be nested" error appears, which is expected (third screenshot).
Is there another way I can I achieve what I'm after here?