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.