14 Replies Latest reply on Jul 7, 2017 5:41 PM by bigtom

How to get the percent difference between 2 numbers of 2 consecutive years?

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!

• 1. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

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

)

1 of 1 people found this helpful
• 2. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Excellent use of percent difference.

• 3. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

A self join matching by company and year could also be used to access the previous asset value in order to compute the desired result.

1 of 1 people found this helpful
• 4. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Thank you for the formula, I will be comparing the same company yearly changes. Thank you for pointing me to ExecuteSQL, i thought it just for Sql databases like mysql and msssql.

I tested the formula and its showing a question mark "?"

Here is what i did

Let(

[

comp = COMPANY::name;

year1 = BALANCESHEET::year ;

year2 = BALANCESHEET::year + 1;

asset1 = BALANCESHEET::assets;

asset2 = ExecuteSQL ( "SELECT BALANCESHEET::assets FROM BALANCESHEET WHERE BALANCESHEET::year =? AND COMPANY::name =?";"";"";year2;comp);

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

];

result

)

I thinks its because of the =? in ExecuteSQL?

• 5. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Hi philmodjunk,

I created a new BALANCESHEET 2 and connected the BALANCESHEET::year and BALANCESHEET::fk_companyname to BALANCESHEET 2::year and BALANCESHEET 2::fk_companyname

Is that correct?

• 6. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

It's a start, but you want a relationship that uses > or < so that you match to all records for the same company with a Year that is less than the current record's year. You may also need to specify a sort order in this relationship to make sure that the first related record is the most recent related record from an earlier year.

I also would not use the company name but rather a company ID field for matching the records. Company Names, like people names are neither unique nor unchanging. They, like people names, are also vulnerable to data entry errors that can have you linking records by a misspelled name and then correcting the error without disconnecting related records is a chore easily avoided if you don't match by names in the first place.

BTW, the ExecuteSQL approach will work. The most obvious error in what you posted is that you can't use TableOccurrenceName::FieldName syntax in the query. You need to use either just the field name or TableOccurrence.Field, note the period instead of ::

1 of 1 people found this helpful
• 7. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Thank you philmodjunk for the tips, I like to try to make ExecuteSQL approach to work, but it does not seem to work.

Does the Year need to be a date type? I set it to a number type.

I changed the formula and  here is my findings

If I removed the "AND" in the ExecuteSQL, there would be a number showing, but if i added it back, it does not show any number, something about syntax error.

Updated formula

Let(

[

comp = COMPANY::name;

year1 = BALANCESHEET::year ;

year2 = BALANCESHEET::year + 1;

asset1 = BALANCESHEET::assets;

asset2 = ExecuteSQL ( "SELECT assets FROM BALANCESHEET WHERE year =? AND COMPANY.name =?";"";"";year2;comp);

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

];

result

)

• 8. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Use the Company foreign key to identify the company since that is what is in the BALANCESHEET table rather than name.

Let(

[

comp = BALANCESHEET::CompanyIDfk;

year1 = BALANCESHEET::year ;

year2 = BALANCESHEET::year + 1;

asset1 = BALANCESHEET::assets;

asset2 = ExecuteSQL ( "SELECT assets FROM BALANCESHEET WHERE year =? AND CompanyIDfk =?"; ""; ""; year2; comp);

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

];

result

)

1 of 1 people found this helpful
• 9. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Hi bigtom,

I've attached the file for you to see, I've done exactly the ExecuteSQL but still get the question mark "?"

What could be the problem?

• 10. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

The Field name 'year' is a reserved word. Use:

\"year\" = ?

Beverly

Sent from miPhone

1 of 1 people found this helpful
• 11. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

beverly wrote:

The Field name 'year' is a reserved word. Use:

\"year\" = ?

Beverly

Sent from miPhone

Nice catch beverly. I assumed "year" was still escaped as provided in the original example.

• 12. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Thank you beverly, using the \"Year\" made a big difference

• 13. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Thank you bigtom for the help, I thought those \" \" are there to replace the field and are not required. I guess I was wrong, everything works after adding those.

• 14. Re: How to get the percent difference between 2 numbers of 2 consecutive years?

Isolation the query to one table using the fk instead of referencing another table might perform better.

I am not certain but this makes sense.