New Topic

Community Topic Idea Report Builder

David Registro

APAC SD Team

Mark McGuire

Yellowfin Support Team

Assigned

Category Report Builder

Product Version 7.4

How to chart calculated aggregate fields?

Stephen Johnson shared this idea ago

Idea Logged

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:

/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.

I like this idea 0

Replies (3)

Guest ●

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

Files:

Reply URL

Stephen Johnson ●

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:

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

URL

Stephen Johnson ●

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.

URL

Guest ●

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

Files:

URL

Stephen Johnson ●

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.

URL

Guest ●

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

URL

Guest ●

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

URL

Guest ●

Hi Stephen,

just wondering how you got along with this matter?

regards,

David

Reply URL

Nick ●

Hi Big Dave,

Has this been addressed in later versions of Yellowfin?

I believe I might be hitting the same problem.

Cheers,

Nick

Reply URL

David Registro ●

Hi Nick,

I've sat down with one of our devs on this, and unfortunately this feature is not currently supported, and we do not have any current plans to address this. It really comes down to how the time series chart aggregations work, which is by simply looking at the data points on the column and then performing one of the following agregations;

bfdd6684e070228215a4c3a56faf14be

There was a discussion on supporting an additional chart aggregation 'Calculated Total' which might get around this, though this is no small feat and no plans to address at this point in time.

This is just a limitation of the unit selection when using calc formulas.

What you would need to do in a case like this is to break up your data sets into the date unit, and have either a drill-down or drill-through.

This way you would have a 'year' report, and then when clicked , can drill into that year and show your month, and again week, day etc...

Hope this all makes sense and sorry for the bad news!

If you had further questions on this, or have problems using the suggested workaround, please let me know.

Regards,

David

URL

Nick ●

Hi David,

Thanks for the detailed response. I will look into the drill-down/drill through suggestion - sounds like that could work.

Regards,

Nick

URL

David Registro ●

No problems Nick. We still have the chart enhancement request open, I just know it's not going to change anytime soon is all. Hope the drill options give you what you need, and if not, please let me know.

Regards,

David

URL

Zack Wilson ●

David,

I was looking for the enhancement for this issue. I have the same question and would like to vote on it. Or, if you know if there has been any traction on this in the last 5 years.

Thanks,

Zack

URL

Mark McGuire ●

Hi Zack,

I hope all is well,

Unfortunately no movement on this. From what I can see that last set of updates were stating that this would require a lot of work to implement of which some workaround/s could be explored... We can see the discussion held with David in the history of this.

Regards,

Mark

URL

Mark McGuire ●

Hi Zack,

I hope all is well and I hope you had a great weekend.

With this I just want to touch base to see here we stand and to see if you had chance to go through my response.

Regards,

Mark

URL

Zack Wilson ●

Mark,

That is unfortunate. I feel this is something that should be a given. I read the text and using drill downs is not ideal when you only look at something by month or week and you want to see it compared to other months (in different years) or weeks (in different months/years). Using the Calculated total only makes sense as a progression of function advancement. This is something that other places do and would be a valuable enhancement for the future.

Is there an Idea somewhere? This conversation is just a question, I assume an idea was created from it?

Thanks,

Zack

URL

Mark McGuire ●

Hi Zack,

Can I ask your availability one of the days this week or next? I am UK based and can work between the hours of 09:00-14:00 UK Time.

Let me know as I would love to discuss this further.

Regards,

Mark

URL

Zack Wilson ●

Mark,

I would be available any day between 13:00 and 14:00 UK time (8am - 9am EDT).

Another use case came up... when more than one dimension/column in a table, you can only take an average of that calculated field, it will not be the the actual calculation.

Thanks,

Zack

URL

Mark McGuire ●

Hi Zack,

How does Tuesday next week, 28th at 13:00 UK Time sound? I've got a busy schedule over the next 2 days then Monday next week is a UK Bank Holiday.

Let me know and I can get this booked in.

Regards,

Mark

URL

Zack Wilson ●

Mark,

Yes. Let's plan on that.

Thanks,

Zack

URL

Mark McGuire ●

Hi Zack,

Thank you for confirming, I have scheduled this in for Tuesday next week, 28th at 13:00 UK, please use this link:

https://v.ringcentral.com/join/090778658

Regards,

Mark

URL

Mark McGuire ●

Hi Zack,

Thank you for our call today. Going forward and as discussed I will further explore our options with the Development Team. In addition I will await your feedback with looking at that possible workaround provided.

I will update you as and when I can.

Regards,

Mark

URL

Mark McGuire ●

Hi Zack,

I hope all is well you had a great weekend, just to update you with this, I have since had some updates from the wider team here at Yellowfin of which this will be investigated going forward, from what we can see discussions were held of which I am hopeful this will be looked at in the coming weeks/months.

I will change this to Idea Logged.

Regards,

Mark

URL