# How to chart calculated aggregate fields?

I have a calculated field that is a ratio (field a/field b). These types of fields cannot be calculated using any of the built in aggregation functions (sum, average, etc.) because they will give you the wrong mathematical results (sum(a/b) instead of sum(a)/sum(b)). In order for this to aggregate correctly, my understanding is that I need to incorporate SQL aggregate functions (sum in this case) into my field calculation at the view level like so:

When I aggregate data at the report level, this seems to work. I set aggregations when appropriate but I leave my calculated field with no aggregation and I get the correct results:

I would expect this to work in exactly the same way when the aggregation is occurring through a time series chart. Unfortunately, this does not seem to be the case. When I add date data and set the date unit to months, the chart calculates an aggregate for the month by summing all of my PPH metrics for each day, giving me a monthly total in the ~110 range when the monthly calculated figure should be in the 3-4 range.

How can I get this chart to aggregate my data correctly? Having calculated metrics that are ratios is not at all uncommon, so I have to imagine Yellowfin supports it some way or another.

Hi Stephen,

unfortunately that is just the way time series charts work, they have to aggregate by the day because there is only able to be 1 place on the time axis for that day - you can't have 2 places alongside each other for the same day.

However, unless I've misunderstood the situation I think that the special total called Calculated Total (Column Formatting->Summary) will be just the thing to help you out here because that is exactly what it is meant to do! It will give you sum(a)/sum(b) rather than sum(a/b), or speaking more generally, it will apply whatever your calculated field's formula is after the sum aggregations have been done.

Please give that a try and let me know how it went.

regards,

David

Hi Stephen,

just wondering how you got along with this matter?

regards,

David

Loading...Comments have been locked on this page!