Aggregate Calculated Fields

[aam82]

Hi, as the Title suggests, I want to aggregate calculated fields. This is not allowed via a standard Aggregation. Is there a workaround for this? Thanks

[Aurora]

Hi aam82

 

You can aggregate calculated fields only if they all have " Number" Data Type in your table. Please check.

 

In works for my Report DataPage!

 

Regards ,

 

Aurora 

[aam82]

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.

[Aurora]

Now it is more clear. Thank you for additional information , aam82 !

 

This is a bug, and as far as I know Caspio will fix it in the next release very soon! 

 

Regards, 

Aurora 

[aam82]

Here to update this: Caspio did add a few features to Aggregation, but this was not one of them. I still encounter the same error.

[Jan]

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?

[aam82]

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.

[nhunsaker]

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?

[Jan]

Hello nhunsaker,

 

Maybe you can use an SQL query in the Aggregation?