Roll up Calculation

[Palpa]

Hi there,

I need to calculate some field based on the date. For Ex;

Balance Forward 8,792,038,358.57
Principal Paid New balance
1-Sep-10 21,224,226.52 8,770,814,132.05
2-Sep-10 12,838,569.65 8,757,975,562.40

Here we have balance forward which is 8,792,038,358.57. We need to substract 21,224,226.52(principal Paid) from the balance forward to generate 8,770,814,132.05 (New Balance) for Sep-1. Now when there is sep-2 then we need to subtract prioncipal paid for 2 Sep from New balance for Sep 1 and keep on going for other dates.


I would appreciate any help regarding this.

 

[lbass]

Insert a running total that does a sum of principal paid. Then create a formula like this for the new balance:

{table.balanceforward}-{#yourrunningtotal}

-LB

 

[Palpa]

thanks LB,

But i am not sure if this will work properly cause for september 2 we need to subtract the principal paid from the new 'new balance' which is begining balance - september 1 principal paid. its just like you are calculating day to day ending balance of your money by subtracting each day expense from your begining balance (begining balance for each day will be the last day new balance.).... i hope this helps


thanks in advance

 

[lbass]

I understand, but if the balanceforward is a field (it would have the same value for each row if placed in the detail section), then this will work. Did you try it?

-LB

 

[Palpa]

Actually i am not putting that in the detail section. it is being grouped by date. so the new balance will go the group footer.

Thanks

 

[lbass]

It still should work--if the principal paid is a simple sum at the group level. Just place the new formula in the group footer. Can you confirm that the balance forward is a database field? If it isn't, please show the formula you used to calculate it.

Please try my suggestion and report back with the results.

-LB

 

[Palpa]

Yes balance forward is a database field...... ...principal paid is the sum of principal paid during the date that's why I grouped it by date...... I will try what you wrote earlier n let u know......

 

[Palpa]

Ok i got..... created three level formula:

Put it in the Account group header
WhilePrintingRecords;
Global NumberVar BalanceForward;
BalanceForward := {BalanceForwardField};

In the date group header or footer where you can display the balance
WhilePrintingRecords;
Global NumberVar BalanceForward;
BalanceForward

In the details to subtract each payment
WhilePrintingRecords;
Global NumberVar BalanceForward;
BalanceForward := BalanceForward - {PrincipalPaidField}

 

[lbass]

Well, that is another approach, but you could have done it the way I suggested, also.

-LB