How to chart calculated aggregate fields?

Stephen Johnson shared this question 1 week ago
Awaiting Reply

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:

/rGGXYiKU4s975gCHdNvYVpEi0LLwukpP51k2WTanXHeyLFBIIYWAEBACQkAICIHbloCYRd22j04yLgSEgBAQAkJACAgBISAEbg4BMYu6OdwlVSEgBISAEBACQkAICAEhsGwIiFnUsnnUUlAhIASEgBAQAkJACAgBIbC0BES5WFq+Il0ICAEhIASEgBAQAkJACCwbAqJcLJtHLQUVAkJACAgBISAEhIAQEAJLS0CUi6XlK9KFgBAQAkJACAgBISAEhMCyISDKxbJ51FJQISAEhIAQEAJCQAgIASGwtAREuVhaviJdCAgBISAEhIAQEAJCQAgsGwKiXCybRy0FFQJCQAgIASEgBISAEBACS0tAlIul5SvShYAQEAJCQAgIASEgBITAsiEgysWyedRSUCEgBISAEBACQkAICAEhsLQERLlYWr4iXQgIASEgBISAEBACQkAILBsColwsm0ctBRUCQkAICAEhIASEgBAQAktLQJSLpeUr0oWAEBACQkAICAEhIASEwLIhIMrFsnnUUlAhIASEgBAQAkJACAgBIbC0BES5WFq+Il0ICAEhIASEgBAQAkJACCwbAqJcLJtHLQUVAkJACAgBISAEhIAQEAJLS0CUi6XlK9KFgBAQAkJACAgBISAEhMCyISDKxbJ51FJQISAEhIAQEAJCQAgIASGwtAT+P6imqGEc2D0hAAAAAElFTkSuQmCC

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:

/D9oNGjVza8DQAAAAAElFTkSuQmCC

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.

/yqRZCtDOGKAAAAAElFTkSuQmCC

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.

Comments (1)

photo
1

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