Jump to content
  • 0

Aggregate Calculated Fields


aam82

Question

8 answers to this question

Recommended Posts

  • 0

more information:

 

My calculated field contains a SQL formula involving 4 fields, each with data type Number. It performs a query and simple arithmetic.

 

But when I select this Calculated Field for Aggregation, I receive the error that 

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

Link to comment
Share on other sites

  • 0

Hello aam82,

 

As far as I know, Aggregations are sub-queries, like SELECT sum(field) FROM table WHERE criteria that are entered on the Search form.

Calculated fields are also a sub-queries, like "SELECT field FROM table WHERE criteria that are entered on the Search and the correspond record" + "SELECT field2 FROM table WHERE criteria that are entered on the Search and the correspond record".

 

So, Aggregations of Calculated fields will be like:

SELECT sum

(

"SELECT field FROM table WHERE criteria that are entered on the Search and the correspond record" + "SELECT field2 FROM table WHERE criteria that are entered on the Search and the correspond record".

)

FROM table WHERE criteria that are entered on the Search form.

 

As far as I know, sub-queries must be enclosed in parentheses. If only standard functions are used in a Calculated fields, Aggregations work correctly. But if you use additional Selects, parentheses are not used and the whole query is incorrect.

 

But I am not sure that I understand all these queries and sub-queries correctly. Maybe, some additional security limits are used for Aggregation fields.

 

Have you written the sub-query that summarize your queries and enter it in the Formula field of an Aggregation?

Link to comment
Share on other sites

  • 0

I have a calculated field that is a sum of fields in a child table to the data source.

 

My datasource is like a category:

 

    category1

 

My calculated field is a sum of items in childtable by categoryid:

 

    item1cost | categoryid1

    item2cost | categoryid1

 

aggr: sum categoryid1

 

The Aggregation I want is a sum of these sums:

 

aggr: sum of (sum of category)

 

My datasource is the category because I must condense the report output to only categories, not the granular items.

Link to comment
Share on other sites

  • 0

I've got a similar issue as aam82.  I get the "Error in the formula" even if the formula appears valid in a calculated field. I basically tried to create an aggregation in a calculated field using SELECT and the SUM function from a child table to tabulate the sum for a particular category. This might not be the proper way to do this but I need to compare the aggregated value to another calculated field. Are there any alternatives to doing these more complex calculations involving making calculations of aggregations against other non-aggregated calculated fields?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...