Calculation Limit?

Discussion created by carlos_panda on Jan 11, 2013
Latest reply on Jan 14, 2013 by carlos_panda

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?