carlos_panda

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...

 

Fields:

date

numberA

numberB

calculationA (= "numberB / number A", stored)

 

Objective:

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)

etc

 

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:

 

tableDATA

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?

 

Thanks

 

 

CP

Outcomes