AnsweredAssumed Answered

Aggregate functions not working?

Question asked by hjgunn@mmedia.is on May 2, 2014
Latest reply on May 2, 2014 by erolst

I'm using aggregate functions to calculate the Sum, Average, Standard deviation etc. of some items in a related table.

 

I have two tables, Items and Summaries

 

The Items table has 5 fields: Key_ProcessYear, Input_Item1, Input_Item2, Input_Item3 and Input_Item4

The summaries table has 11 fields: Key_ProcessYear, Sum_Item1, StDev_Item1 ... Sum_ItemTotal, StDev_ItemTotal.

I then have a relationship based on Key_ProcessYear

 

===

 

Calculating the Summaries for each item through the relations works fine.

Calculating the StDev for each item through the relation also works fine.

 

As I suspect you all know, doing something like Sum( Items::Input_Item1 ; Items::Input_Item2 ; Items::Input_Item3 ; Items::Input_Item4 ) does not give me the sum of Summaries::Sum_Item1 + Summaries::Sum_Item2 + Summaries::Sum_Item3 + Summaries::Sum_Item4.

It actually returns Items::Input_Item1 + Items::Input_Item2 + Items::Input_Item3 + Items::Input_Item4.

 

===

 

I know I can write a script to perform the calculation and run it everytime needed.

That is everytime I display the layout containing the aggregate results and everytime I change a value in the items table if a layout showing calculations based on the edited value is being displayed...

 

But is there really no simpler solution?

Outcomes