How arithmetic and aggregations interact in Calculated Fields

This article is intended to give some brief examples of how and where the different aggregation options within the Report Builder are plugged into the SQL statement and subsequently applied to reports.

Let's say you have a data set with the following values:

And you attempt to simply divide field A by field B.

If you divide two un-aggregated fields, you’ll see the following:

The reason the data displays like this is because nothing is aggregated by default.

So it’s now technically a Group By variable – so we’re seeing every individual row value for this because Yellowfin recognizes the Calculated Field as a Group By variable:

However, most of the time when using metrics and utilizing division, multiplication, etc., you’ll want some form of aggregation in there.

Let’s say we want to add a Sum Aggregation to this Calculated Field:

This adds a SUM on the outside of this Calculated Field:

What this is doing is going down every single row and summing each, which, to use our original A/B example, would look like this:

Meaning the result set here would equal 1.15.

Chances are though, the desired result would actually be 6 / 15, i.e, the division of the sum value of each column, which would instead equal 0.4:

This is how that would look in the Calculated Field builder:

Here's the result:

Is article helpful?