How to Achieve a Weighted Average Rate Cross-Tab Report
Objectives
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).
Requirements
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.
Problem
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).
Ask
Is there another way I can I achieve what I'm after here?
The same question 
Hi Andrew,
Thanks for reaching out. This works for me when I don't use aggregated values at all:
When I start aggregating in the Calculated Field, I begin seeing errors:
Different RDBMS' can have different errors however, as such, what type of database corresponds to your Data Source? I'd also be curious to see the full trace for the error you're seeing. This should be found in your yellowfin.log, so you if you can re-replicate and provide a timestamp for replication along with a copy of your yellowfin.log file that would be great.
I'm also curious as to whether when selecting your values in the Calculated Field builder, are you seeing the fields pre-aggregated (i.e., a
or some other aggregation symbol), or is it just blue?
The values should look like this:
I'm just wondering if these values are aggregated at the View Level, in case they may be getting aggregated in the equation without maybe realizing it.
One other test, can you try your simple calculated field equation in the database directly just to make sure that query works?
Either way, I think the full trace in the yellowfin.log will shed some light on what's going on behind the scenes, so let's definitely take a look at that.
Regards,
Mike
Hi Andrew,
Thanks for reaching out. This works for me when I don't use aggregated values at all:
When I start aggregating in the Calculated Field, I begin seeing errors:
Different RDBMS' can have different errors however, as such, what type of database corresponds to your Data Source? I'd also be curious to see the full trace for the error you're seeing. This should be found in your yellowfin.log, so you if you can re-replicate and provide a timestamp for replication along with a copy of your yellowfin.log file that would be great.
I'm also curious as to whether when selecting your values in the Calculated Field builder, are you seeing the fields pre-aggregated (i.e., a
or some other aggregation symbol), or is it just blue?
The values should look like this:
I'm just wondering if these values are aggregated at the View Level, in case they may be getting aggregated in the equation without maybe realizing it.
One other test, can you try your simple calculated field equation in the database directly just to make sure that query works?
Either way, I think the full trace in the yellowfin.log will shed some light on what's going on behind the scenes, so let's definitely take a look at that.
Regards,
Mike
Hi Andrew,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Andrew,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Mike,
I haven’t had a chance to dive into your suggestions, but at first glance, the solution that works for you is what I need to avoid – it looks like you’re summing a percentage, where I need to calculate the percentage of some underlying sums.
Will take a more detailed look and get back to you as soon as possible next week.
Thanks for checking in.
Andrew
From: Yellowfin Support <support@yellowfin.bi>
Date: Friday, June 11, 2021 at 12:57 PM
To: Andrew Clauson <andrew@profinancial.bc.ca>
Subject: New Comment in "How to Achieve a Weighted Average Rate Cross-Tab Report"
Hi Mike,
I haven’t had a chance to dive into your suggestions, but at first glance, the solution that works for you is what I need to avoid – it looks like you’re summing a percentage, where I need to calculate the percentage of some underlying sums.
Will take a more detailed look and get back to you as soon as possible next week.
Thanks for checking in.
Andrew
From: Yellowfin Support <support@yellowfin.bi>
Date: Friday, June 11, 2021 at 12:57 PM
To: Andrew Clauson <andrew@profinancial.bc.ca>
Subject: New Comment in "How to Achieve a Weighted Average Rate Cross-Tab Report"
Hi Andrew,
Thanks for your response. Sounds good! Please let me know what you find.
Regards,
Mike
Hi Andrew,
Thanks for your response. Sounds good! Please let me know what you find.
Regards,
Mike
Hi Andrew,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Andrew,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi Andrew,
I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Hi Andrew,
I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Thanks, Mike. Sorry, I still haven’t had much of a chance to dive into your suggestions, though I’m pretty confident I’ll end up in the same place. Feel free to mark as closed for now.
From: Yellowfin Support <support@yellowfin.bi>
Date: Friday, June 25, 2021 at 12:21 PM
To: Andrew Clauson <andrew@profinancial.bc.ca>
Subject: New Comment in "How to Achieve a Weighted Average Rate Cross-Tab Report"
Thanks, Mike. Sorry, I still haven’t had much of a chance to dive into your suggestions, though I’m pretty confident I’ll end up in the same place. Feel free to mark as closed for now.
From: Yellowfin Support <support@yellowfin.bi>
Date: Friday, June 25, 2021 at 12:21 PM
To: Andrew Clauson <andrew@profinancial.bc.ca>
Subject: New Comment in "How to Achieve a Weighted Average Rate Cross-Tab Report"
Hi Andrew,
Thanks. Sounds good. When you have the chance to revisit this, if you respond here, I'll be happy to assist further.
Regards,
Mike
Hi Andrew,
Thanks. Sounds good. When you have the chance to revisit this, if you respond here, I'll be happy to assist further.
Regards,
Mike
Replies have been locked on this page!