I feel that I should know the answer to this but just can't work it out...
calculationA (= "numberB / number A", stored)
To create a new calculation field (calculationB) that contains the cumulative product of calculationA in descending date order:
date (most recent first), calculationA, calculationB (= result)
date 1, cA1, cB1 = 1 (most recent record to always return 1)
date 2, cA2, cB2 = 1 x cA1
date 3, cA3, cB3 = 1 x cA1 x cA2 (i.e.: cB2 x cA2)
date 4, cA4, cB4 = 1 x cA1 x cA2 x cA3 (i.e.: cB3 x cA3)
date 5, cA5, cB5 = 1 x cA1 x cA2 x cA3 x cA4 (i.e.: cB4 x cA4)
So, in essence, calculationB is always equal to the product of calculationA and itself (both from the previous record).
I can make this work using a calculation based on a relationship:
tableDATANXT (contains all records with a date greater than the current record, sorted by ascending date)
tableDATA::calculationB = Case ( tableDATA::Date = tableDATA::FirstDate ; 1 ; ( tableDATANXT::calculationA * tableDATANXT::calculationB ) )
BUT... only some calculate with the remainer displaying a "?". The actual number that calculate is variable - perhaps the first 177 out of 769 records but appears to be dependent on whether the results are displayed in a table or a portal.
Clearly the calculation works, just not enough times.
Does anyone know of a better way to calculate a running balance as the product (rather than the sum) of a given field in a specified (date) order?