Average (variable) value between two changing date ranges

[4Justme2]

I have a numeric calculation field called cAgeAtTheTime which calculates a person's age (to the year and month) on a specific serviceDate. People often participate on many different serviceDates. For example, on one serviceDate their cAgeAtTheTime is 22.3 (22 and 3 months old) and if they attend an event six months later their cAgeAtTheTime would be 22.9.

If searching between a range of serviceDates I want to create a calculation that results in the average of cAgeAtTheTime during the specified serviceDate range. What would the calculation be that would give me such a result?

[comment]

First, only numerical data can be averaged - so you'd probably want to calculate the age in months, average that, and then split it to years & months.

The other thing is how meaningful such calculation is. Suppose your date range is 1/1/2011..12/31/2011. Adam attended several events at the beginning of 2011, while Betty attended some towards the end of that year. Adam and Betty were born on the same day. Should their average for the given date range be different?

[4Justme2]

Let me further clarify. I need the average age to be person specific. So if Adam attended several events in the first quarter of the year his averge age would be different from Betty attending later in the year--because she would have been older than Adam at the time she attended.

[comment]

Ok, then. If you find all the attendances in the given range, and sort them by PersonID, you can use a summary field to average the data (again, provided it's numerical).

Alternatively, you could create another relationship between People and Attendance, filtered by date range. Then use a calculation field in the People table to average the related ages.

[4Justme2]

Ahh, two very helpful suggestions. Thank you so much... mp

[4Justme2]

First, only numerical data can be averaged - so you'd probably want to calculate the age in months, average that, and then split it to years & months.

What would the calculation look like first, to calculate age in months, then average the age, and split into years and months. Does anyone have a calculation they could share with me to do that?

[comment]

to calculate age in months

Doesn't your current calculation do that first?

http://fmforums.com/...te/#entry395374

[4Justme2]

I calculate the years using: (resulting in something like 14 for example)

Year ( Get ( CurrentDate ) ) - Year ( DOB) - If ( Get ( CurrentDate ) < Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( Get ( CurrentDate ) ) ); 1 ; 0 )

Then I calculate remaining months portion: (resulting in something like 5 for example)

Mod ( Month ( Get ( CurrentDate ) ) - Month ( DOB ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day (DOB) ; 1 ; 0 ) ; 12 )

Then I concatonate the two figures together:

cAgeInYears&"."&cAgeInMonths (resulting in something that looks like 14.5 for example)

Although I calculate this as a number it sounds like I won't be able to evaluate an average on the concatonated field? Is that correct? So if my average needs to be based on months rather than the concatonated field how do I get the entire portion of my result in months rather than just the reminder?

[comment]

I see. I thought you were using the formula suggested in the other thread...

Anyway, if you know the number of whole years and the number of remaining months, you can calculate the number of elapsed months very simply by =

12 * years + months

Make the result a Number, use a summary field to average it, then another calculation field to split it back into y and m (see the other formula how).

[4Justme2]

Geez, that's so simple. I don't know why I was having such a brain-cramp around it. Thank you so much! :yep: