Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Stephen Johnson shared this problem 10 months ago
Defect Logged

I am having problems using the ratio pre-built advanced function. Here is an example of setting up a ratio to calculate price per gallon:

fc0c5ee7ea2c4a62f25b4b1b809e68b4

This one works. When I go to test the function it aggregates exactly as I would expect when aggregating multiple records:

f5bea75b16e01cc02e1be87f35c85d74

Here I am trying to set up another ratio, this time to calculate an on time performance ratio:

f2989c585233761d71e9d80c9a5530ec

Now when I try to test it, I get this cryptic error message and YellowFin is trying to perform a summation on my ratio or something:

3afe3afe09836a7d8a96a329b28627c0

How can I get YellowFin to stop trying to sum my ratio? The YellowFin wiki has no documentation on these pre-built functions: https://wiki.yellowfin.bi/display/USER74/Calculated+Fields

Comments (13)

photo
1

When I look at the prepare step I see similar differences. One is listing the summation symbol and the other is not. I don't understand why.

d107fbe6a5318364b8bfc2780d0a2992 d3ad1c6aeb17b3cfb69980156eb99655

photo
1

I ran a monitor on my SQL Server and was able to capture the query Yellowfin is attempting. The problem is the SUM expression wrapping the other SUM. I don't understand what part of Yellowfin is triggering this and how to turn it off. Obviously it's not doing that for the PPG ratio I setup above.

229261c96a33d77428a09e448c5774c4

If I manually remove that in SQL Server Management Studio, the SQL works exactly as expected.

photo
1

I have gotten my OTP Ratio to start working. I went through all of the fields in my view, made sure all of the fields that were intended as dimensions were correctly marked as dimensions (some were not), and made sure every non-pre-built-calculated-field metric had a default aggregation selected. Once I did this, the OTP Ratio started working.

Then I tried reversing the step I had taken (by setting default aggregation to None and changing dimensions back to metrics) to reproduce the broken condition, but I could not. Somehow once the ratio started working it stayed working.

I guess it's just another mysterious bug in YellowFin...

59730e1915e9d7b454d9d62059e0928a

photo
1

Created a new view and tried to use the ratio function and this problem has returned. My strategy of ensuring that all of the other fields have default aggregations and everything is labeled properly is not working. This is getting very frustrating.

6cf83f7b5ffca7e20fb2fd5329f0219e

8cd78d53131b22aebe6152189cfd89de

photo
1

If I go to the advanced functions menu item for the sum that is not working and choose the no aggregation option:

38b1dfbe681ffa7d551d5902c2b58fd1

I can get Yellowfin to generate the proper SQL:

f3c047ed09c6d2c043642d412ee3440e


When I execute this SQL in SQL Server Management Studio I get the expected results:

65bc087fae1b0d0e478751959ca14290

But Yellowfin refuses to execute the correct SQL and give me the results I want. I guess the software is not happy unless it's giving me errors and not working:

c85c83eb18e550aba3aff8f24d40c610

photo
1

I think I finally figured out how to fix this issue. The problem seems to be that when you choose a pre-defined calculated function, you no longer have the ability to choose a default aggregation. This suggests that there is no default aggregation for this field, but that is not true.

308be4d8367bede9ad098ee6d2ae6913

To fix this I had to change my calculated field from a pre-defined function back to a simple function (it doesn't matter what your function is, I just put in the number 5):

c459ff4a260d79302d84a8b3b7d35175

This re-enables the default aggregation selection, and you need to change it to None.

46892849e2f3ffa2953d6a5428b199f3

Now you can change your calculated field back to the pre-defined function you were using before (ratio in my case) and when you try to use the field in reports, Yellowfin will stop trying to apply that secret default aggregation and breaking your SQL

459fc0c9a6053a54b0a54bf1bdcd56f1

The Yellowfin devs need to fix this by making sure that when the user sets up a pre-defined calculation and you disable the users ability to change the default aggregation, that default aggregation variable in the background needs to be set to None.

photo
1

Hi Stephen,

Thanks for reaching out and for detailing your thoughts and actions here. Glad you appear to have gotten to the bottom of this.

I'll investigate and replicate this (and will likely be logging it it seems) on Monday morning/afternoon.

Regards,

Mike

photo
1

Hi Stephen,

I've had a chance to test this, but am thus far unable to replicate what you're seeing.

This does not occur using the embedded HSQL database:

/OHLHi5Y+9B5TTLoAgeNqkmt9CoRplrXZO7jv1d04a+tNpuNrq4uQkJCCA8PJyjI8XNfpd90001XSWsmXw1lQKk2XrhwgY6ODq2t6niqtv8fsHJ3ARVScEkAAAAASUVORK5CYII=

I then tried this against MS SQL Server, thinking this may be a driver and/or RDBMS related issue again, but I'm still not seeing the corresponding value beingn applied as a SUM aggregation:

/C8iqXVMKvGbiQAAAABJRU5ErkJggg==

/ATWSUJAFnxtMAAAAAElFTkSuQmCC

/QogwhGNkbNgAAAABJRU5ErkJggg==

This seems to imply it's either your underlying data and/or how the rest of the report or view is setup, may be causing this in your case.

As such, can you perhaps provide more information on how the 'OTP Numerator' field is setup so I can try and replicate this?

When I put 1.0 as the Denominator in my example all appears to be working as it should. In my case, the 1.0 is being applied against a, at the report level: SUM Aggregation Numeric field, which is a SUM Decimal value at the View level, as well as a Decimal at the db level. If you can highlight the differences here that'd be great.

Thanks,

Mike

photo
1

Hi Stephen,

I just wanted to check in and see how things are going regarding my latest reply.

Regards,

Mike

photo
1

Have you tried following my solution in reverse?:

  1. Change the ratio calculated field back to a simple calculation field
  2. Change the default aggregation to summation
  3. Change the calculated field back to the ratio pre-defined type
  4. Try to aggregate in rport

What build are you using? We're still on 20180515

photo
1

Hi Stephen,

Thanks for your response. While I can break the report by following the steps you've laid out. I can't get the calculated field to default to Sum aggregation upon building the calculation in either 20180515 or the latest build:

/wa0G9DZr5Zyvd3bIe7282RsJ5I0AU+V5m1GOhwRIgARIINcE6LhzPb0cHAmQAAmQQN4I0HHnbUY5HhIgARIggVwToOPO9fRycCRAAiRAAnkj8P8BrTnaUG+ZJLAAAAAASUVORK5CYII=

However, after following the steps you've laid out in your last response I can replicate what you're seeing:

/D5Juqf1k+CNCo3u5o82CxnV3fJbZDAxcPsPxnAtLSEulcrZj1vVeawGeQuYkJACAgBISAEhIAQEAJCQAgIgYUmIEMiF5q4lCcEhIAQEAJCQAgIASEgBISAEDBIQAI2g6BETAgIASEgBISAEBACQkAICAEhsNAEJGBbaOJSnhAQAkJACAgBISAEhIAQEAJCwCABCdgMghIxISAEhIAQEAJCQAgIASEgBITAQhOQgG2hiUt5QkAICAEhIASEgBAQAkJACAgBgwQkYDMISsSEgBAQAkJACAgBISAEhIAQEAILTUACtoUmLuUJASEgBISAEBACQkAICAEhIAQMEpCAzSAoERMCQkAICAEhIASEgBAQAkJACCw0AQnYFpq4lCcEhIAQEAJCQAgIASEgBISAEDBIQAI2g6BETAgIASEgBISAEBACQkAICAEhsNAEJGBbaOJSnhAQAkJACAgBISAEhIAQEAJCwCABCdgMghIxISAEhIAQEAJCQAgIASEgBITAQhOQgG2hiUt5QkAICAEhIASEgBAQAkJACAgBgwQkYDMISsSEgBAQAkJACAgBISAEhIAQEAILTUACtoUmLuUJASEgBISAEBACQkAICAEhIAQMEpCAzSAoERMCQkAICAEhIASEgBAQAkJACCw0AQnYFpq4lCcEhIAQEAJCQAgIASEgBISAEDBIQAI2g6BETAgIASEgBISAEBACQkAICAEhsNAEJGBbaOJSnhAQAkJACAgBISAEhIAQEAJCwCABCdgMghIxISAEhIAQEAJCQAgIASEgBITAQhOQgG2hiUt5QkAICAEhIASEgBAQAkJACAgBgwT+P0yIcE2wOHQlAAAAAElFTkSuQmCC

I agree a good safety would be to make it so that when a user sets up a pre-defined calculation, the users ability to change the default aggregation is not disabled, considering the default aggregation variable in the background needs to be set to 'None'.

Of course this no longer occur occurs if you switch the calculated field to a Simple formula and remove the Aggregation, so this would be the workaround here.

I've gone ahead and logged this as a defect. Any potential updates regarding this will be posted here.

Regards,

Mike

photo
1

It seemed inconsistent for me. Sometimes the calculated field would default to the sum aggregation upon creation and sometimes it would not.

photo
1

Hi Stephen,

Noted. I've added to our internal task that this is particularly problematic if/when a Pre-Defined Calculated Field defaults to a Sum aggregation, though I was not able to replicate this particular aspect of the issue.

I'll keep you posted.

Regards,

Mike