How to Achieve a Weighted Average Rate Cross-Tab Report

Andrew Clauson shared this question 2 years ago
Answered

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?

Replies (8)

photo
1

Hi Andrew,

Thanks for reaching out. This works for me when I don't use aggregated values at all:

/617f2a8bf08cab54b1ed3900162a47ff

When I start aggregating in the Calculated Field, I begin seeing errors:

/d5a49def7ffd2b66d21d2046046a6585

/4df324d80badeaa78e4c725ef44d136f

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 /24fb1c1df66437ad94bd81f3cc956f95 or some other aggregation symbol), or is it just blue?

The values should look like this:

/48b043347d4e01366285ae962c98b185

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

photo
1

Hi Andrew,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

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"

photo
1

Hi Andrew,

Thanks for your response. Sounds good! Please let me know what you find.

Regards,

Mike

photo
1

Hi Andrew,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

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

photo
1

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"

photo
1

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

Leave a Comment
 
Attach a file