Formula for a groupwise field. Kindly help

[Former Member]

Hi All,

I have grouped all the transaction buy currency Ex below

AUD

Contract1  Balance1 Interest1

Contract2  Balance2 Interest2

Contract3  Balance3 Interest3

USD

Contract1  Balance1 Interest1

Contract2  Balance2 Interest2

Contract3  Balance3 Interest3

Initially I used running total for Balance and Interest then divided by interest/balance. It did not work rather it is only picking last Interest3/balance3.

Kindly help to resolve and let me know if you require further input to resolve this. Thanks

[abhilash_kumar · Accepted solution]

Hi Singarayan,

If you're trying to display the totals on Group Footer, you should place the Running Total on the Group Footer too.

In the 'Reset' section of the Running total, make sure it is set to reset on the Group for currency.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you for your quick response. Hereby I have attached the supporting document for your reference. My calculation formula based on dividing 2 running total which total interest / Balance and it is picking last entry rather applying for a group. Hope you can provide solution quickly. Let me know if you require any other information. In the attachment the answer should be around 4.003

Regards,

Singarayan D

[Former Member · Accepted solution]

Adding a clear attachment Abhilash...

[abhilash_kumar · Accepted solution]

Could you point out where it's only using the last entry in the group?

-Abhilash

[Former Member · Accepted solution]

Dear Abhilash,

You can see in the report AUD, CFP and CNY currencies are grouped.

803078000/200700000= 4.00138 but in the report it is taking 2.84

Regards,

Singarayan D

[abhilash_kumar · Accepted solution]

What is the formula you've used to calculate the 'Total Rate' on the footer?

It should looks something like this:

{#Interest_Amount}/{#Curr_Balance}

Where both these fields are Running Totals.

-Abhilash

[Former Member · Accepted solution]

Dear Abhilash,

I am getting error as below for my formula look like this

{#fm_interestAmount}/{#vSDCustDeposits.Balance} - #fm_interest field name not known. Kindly advise and let me know if you require further details

Regards,

Singarayan D

[abhilash_kumar · Accepted solution]

Are you sure you've created these running totals?

{#fm_interestAmount}

-Abhilash

[Former Member · Accepted solution]

Dear Abhilash,

I was about to tell you. Yes, it is a running total but those values derived from a formula. All the interest amount columns are derived from a formula. For those value, i applied running total. Thanks

Regards,

Singarayan D

[abhilash_kumar · Accepted solution]

So, you're using a 'formula' and not an actual 'Running Total' field created from the 'Field Explorer'?

Would you be able to attach the report with saved data (or send it to my email) please?

-Abhilash

[abhilash_kumar · Accepted solution]

The @fm_weightedavg formual should have this code:

If Sum ({vSDCustDeposits.Balance}, {vSDCustDeposits.Curr}) = 0 then 0 else

Sum({@fm_InterestAmount}, {vSDCustDeposits.Curr})/Sum ({vSDCustDeposits.Balance}, {vSDCustDeposits.Curr})

-Abhilash

[Former Member · Accepted solution]

Dear Abhilash,

This is great help and thank you very much.

Regards,

Singarayan D

[Former Member · Accepted solution]

Hi Singarayan,

Please find the solution below.

First apply running totals on balance and interest then place them in group footer, now create a formula  interest /balance. Place the formula  in details tab.

Now create one more running total on formula which u have created before and place it in group footer.

In running total field select on change of field option for evaluate and reset buttons.