How to chart calculated aggregate fields?

Stephen Johnson shared this question 3 months ago
Answered

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 (8)

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

photo
1

I went ahead and selected this option, but it doesn't seem to have had any effect.

Let me try to clarify the issue I'm having. When I add a date metric from my date hierarchy in the Data step, the data is aggregated correctly:

/D9+9Qy7sPeKNAAAAAElFTkSuQmCC

When I select the month metric from my date hierarchy instead, the data is aggregated correctly:

/wCWQlWWoIlsgIAAAAASUVORK5CYII=

When I chart the date metric in the Charts step, the data is charted correctly.

/h+QshVZFVP3rQAAAABJRU5ErkJggg==

Next, I want to change the time axis units from Days to Months. In my mind, this should be just like changing from days to months during the data step:

/A7zNrOQ3MgTtAAAAAElFTkSuQmCC

Unfortunately when I make this unit switch, Yellowfin does not intelligently perform this aggregation like it did in the Data Step. For some reason this chart aggregation ignores the aggregation function I have specified for the field in question (in this case a lack of aggregation because the calculated field itself uses aggregate functions) and performs a simple sum, which is wrong:

/aQAACEIBAdgn8f+A+qh5M20Z2AAAAAElFTkSuQmCC

photo
1

The systems logic for aggregations should be consistent throughout the system. During the Data step, the system recognizes that I have not specified an aggregation but that my calculated field has aggregated metrics. The system should apply the same logic when aggregating in the Charts step.

photo
1

Hi Stephen,

thanks for all the screenshots and and explanations, I understand now exactly what you're saying and will discuss with my colleagues whether this is an enhancement request or not, I actually think it's a bug, because if the calculated field's formula is sum(x)/sum(y) and this is correctly calculated when the unit = day, but when the X-Axis unit is changed from day to month then the formula changes to sum(sum(x)/sum(y)).


In the meantime, as a workaround, if you create a Date Function in the view that calculates the Month of Event Date then this should correctly show your PPH per month in the chart.

I have tested this out over here with our Ski Team demo data, I used Year instead of Month but it worked correctly. In the attached video you can see that the formula SUM(Invoiced Amount) / SUM(Invoiced Estimate) should always produce a figure under 20, but when I change the unit of Invoiced Date to Year then the calc field sums all of the daily calculations and produces an incorrect total of over 800 for the year 2010. But then when I use the "Year" dimension (created by the Date Function at the view level) instead of Invoiced Date then it correctly shows around 10 for the year 2010.

I hope that is of some help to you for your reporting requirements, please let me know what you think.

regards,

David

photo
1

Yeah...I demonstrated the same thing in my screenshots above. We can correctly show the data using whatever time basis we need, we just can't change between them.

photo
1

Hi Stephen,


yes I understand, and so I have raised product defect YFN-11357 so that this gets fixed.

Thanks for notifying us of this issue and apologies for the inconvenience caused by it.


regards

David

photo
1

Hi Stephen,

the developers have just reviewed that defect I raised, they say I was wrong insofar as it is not a defect but instead, an enhancement request. That means the next step will be for the product team to review the enhancement request and see if they think it should go onto the roadmap for the next version. Will let you know what they come up with.


regards,

David

photo
photo
1

Hi Stephen,

just wondering how you got along with this matter?

regards,

David