Creating Numerous Calculations Between Child Records

[GisMo]

I have two tables
Parent Table - "Company" 

and Child Table = "Data"

each Company has several related "Data" records individualized by a "YEAR" field.

Each Year, we import new Data for that year.

I need to do some computations between the annual data  records for each Company. For example if current year is 2015, average X from the last 3 years(2015, 2014, 2013). I need to do several calculations like this.

Bonus, is there a way to automatically update these calculations when the new data comes in for 2016, so then the average would automatically become 2016, 2015, 2014?

I'm looking for a maintainable solution and I feel like it's a combination of table relationships and some nice calculations.

Thanks

[Kris M]

Combine a sub summary report with a find on the three years you need to aggregate and your I'll bet that will get you 95% there.

http://help.filemaker.com/app/answers/detail/a_id/6198/~/simple-example-of-a-subsummary-report

http://help.filemaker.com/app/answers/detail/a_id/790/~/creating-a-sub-summary-report-by-month-in-filemaker-pro

 

 

Edited June 16, 2015 by Kris M

[GisMo]

Hey Kris,

I don't think Sub Summary Reports will work here. Also, I am not using list views for this data. I am looking directly at the company record...Unless there's a technique I'm missing?

[comment]

 

Each Year, we import new Data for that year.

​Your description is not quite clear. How many data records does a company have per year?

 

In any case, you could define a cLast3Years calculation field in the Companies table =

Year ( Get ( CurrentDate ) ) - Get ( CalculationRepetitionNumber ) + 1

Make the result type a Number, and set the number of repetitions to 3. Then define a relationship between Companies and a new occurrence of the Data table as:

Companies::CompanyID = Data 2::CompanyID
AND
Companies::cLast3Years = Data 2::Year

Now you can use a formula such as:

Average ( Data 2::Value )

to get the average value of the company's data for the last three years.

Edited June 16, 2015 by comment