Hello,

I'm trying to convert a simple excel file containing balance sheet of different companies into a filemaker file and hit upon this problem.

The data is

Company, Year, Asset,

CompanyA, 2000, 10000

CompanyA, 2001, 20000

CompanyA, 2002, 21000

CompanyA, 2003, 50000

ComapnyB, 2000, 2000

CompanyB, 2001, 1000

CompanyB, 2002, 500

The formula is

(A-B)/A * 100%

In Excel, its very easy to select a cell and have it calculate the solution.

In Filemaker, I don't know how to get the number of the first year to use for the second year.

Is this possible in Filemaker?

Thank you!

You are not really clear if you are wanting to compare yearly values of CompA and CompB or CompA and CompA. Also not specific if you are evaluating from the year or the next year. Your percentage could be +/- depending on this.

Here is an example of comparing A to A for consecutive years from the previous year perspective.

Let(

[

comp = Company;

year1 = Year;

year2 = Year + 1;

asset1 = Asset;

asset2 = ExecuteSQL ("SELECT \"Asset\" FROM \"YourTable\" WHERE \"Year\"=? AND \"Company\" = ?"; ""; ""; year2; comp);

result = ((asset2-asset1) / asset2) * 100

];

result

)