Percent of total

[paulmtl]

Hi all,

How to create the formula to calculate the percentage out of the total in general format can be used all along ? from mytable.dbf

account amount percentage

11111 100 0.16
11111 200 0.34
11111 300 0.50
---------------
subtotal 600

22222 200 0.25
22222 200 0.25
22222 400 0.50
--------------
subtotal 800


I got the columns account , amount but need to calculate the percentage but be carefull , each time the account changes the formula of percentage should changed accordingly.

Thanks in advance,

 

[vazagothic]

Question:
Do you need to store the percentage in the table ?

If it needs to be calculated on-the-fly, then a single change to one row will force you to replace percentage in ALL rows.

If you really need to do it that way, then I believe the fastests way would be:

SELECT mytable
SUM ALL amount TO lnTotalAmount
REPLACE ALL percentage WITH amount/lnTotalAmount

I would really rething this approach, and try to modify the requirements, or just use a Column with automatically updated value.

 

[vazagothic]

To extend my answer, if you show the data in lets say MyGrid in columns AccountColumn, AmountColumn and PercentColumn (this one is an additional one), simply do:

In the Form.MyGrid.AmountColumn.Text1.Valid put:

SUM ALL amount TO lnTotalSum
THISFORM.MyGrid.PercentColumn.Refresh()

Then in the Form.Init put:

PUBLIC lnTotalSum
THISFORM.MyGrid.RecordSource = "mytable"
SELECT mytable
SUM ALL amount TO lnTotalSum
THISFORM.Grid1.PercentColumn.ControlSource = "mytable.amount / lnTotalSum"