1 of 1 people found this helpful
To make a calculation with self relating data, create another table occurrence of the same table. You'll have to give it a different name and set up the relationship between the table occurrences. It works fine even though it is the same table.
One thing many of us having been trying since FM12 is to minimize the number of table occurrences since sometimes our graphs get so big. So if I need a relationship for only one calculation and will never need that relationship again, then I use ExecuteSQL and a join statement to get me the same results without creating another table occurrence. However, if you plan to make regular use of their relationship back to itself, go with the additional table occurrence.
This is a simple example of a dynamically controlled relationship, accumulating original data and using them in altered form to query the same table.
Thank you very much for the reply and the attachment. The attachment was definitely worth more than a 1000 words.
Is it also possible to use this concept to skip certain data points but not skewup the calculation?
Here is an extension to the concept: excluding specific datapoints manually. Another would be to include only datapoints within specified ranges etc.
As Taylor stated: you can either create utility relationships to collect these data in your front end, or (in FM12) use ExecuteSQL in order to keep your Relationship Graph uncluttered.
I'll use your example to highlight a very real problem with how this works.
The field "OneRecordOfGrowthRateOfBacteriaA" is used twice. Each usage requires FMP to gather the data required to produce the result.
The field "AverageGrowthRateOfBacteriaA" (by its name) is an aggregate. Its used twice so multiply the single gather time by 2.
As you can see each usage results in a gather time to get the data.
However if this is done in a Let() function the data gathering can be reduced to a single time per value.
Let ( [
OROGROBA = OneRecordOfGrowthRateOfBacteriaA ;
AGROBA = AverageGrowthRateOfBacteriaA ;
OROGROBB = OneRecordOfGrowthRateOfBacteriaB ;
AGROBB = AverageGrowthRateOfBacteriaB ;
O_A = OROGROBA - AGROBA ;
O_B = OROGROBB - AGROBB
SUM ( ( O_A ) * ( O_B ) ) / SQRT ( SUM ( (O_A ) ^ 2 ) * SUM ( ( O_B ) ^ 2 ) )
If the "AverageGrowthRateOfBacteriaA" field is itself a gathering calculation it should probably be inserted in full inside this calculation instead of as a separate field. The reason is dependencies. When gathering data FMP must resolve dependencies and each level requires more gathering of data. Sometimes multiple times. Unless all fields are local its better to resolve all values within a single calculation than it is to use fields to get related sub-values.