# Weighting summaries by date

This topic is 2348 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Can anyone explain how FM treats a summary field if weighted by date? For example, say I have one transaction each month over 5 years. I want an average of these that would be a total each month's transaction values divided by 60. What if this were weighted by date?

##### Share on other sites

4 minutes ago, enquirerfm said:

What if this were weighted by date?

That makes no sense.

A weighted average is similar to an ordinary average, except that instead of each of the data points contributing equally to the final average, some data points contribute more than others [https://en.wikipedia.org/wiki/Weighted_arithmetic_mean]. Weighting by date (or by some derivative of date) would only make sense in a situation where more recent values are more significant than values obtained less recently.

What is it that you really want?

##### Share on other sites

Well, FM provides the weighting opportunities based on a variety of fields and as I have a date field it seems a possibility.

Quote

Weighting by date (or by some derivative of date) would only make sense in a situation where more recent values are more significant than values obtained less recently.

Yes, absolutely. Let me give an example. I have data on sales at auction which go back 5 years say. The sales amounts will vary over the period - sometimes higher, sometimes lower but often there is an underlying trend (upwards) reflecting scarcity, inflation etc. In determining an average price of certain auction lots, it might make sense to attribute more importance to the most recent prices rather than those sold 5 years ago and it is also possible that using, say a quantity weighted average, there will be more items sold 5 years ago than today which would weight the prices towards the older items. However, without knowing how exactly FM might apportion the weighting for the date it's not that easy to know whether it makes sense or not. What do you think?

##### Share on other sites

21 hours ago, enquirerfm said:

However, without knowing how exactly FM might apportion the weighting for the date it's not that easy to know whether it makes sense or not. What do you think?

I think you are approaching this from the wrong direction. You should start by determining what importance you want to assign to the passage of time in-between transactions (a purely business decision), then ask how to implement this.

I think it's fairly safe to say that a simple weighting by date will not fit your purpose - if only because the relative weighting would change as time goes by. To illustrate, consider these two scenarios: in the first scenario, there are two records a week apart: say Jan 1 and Jan 7 this year. In this scenario, the first record's weight will be 735964 and the second record's weight will be 735970. This difference is negligible and the result is that both records will contribute equally to the average.

In the second scenario everything stays exactly the same, except the year is changed to 0001. Here the first record's weight will be 1 and the second record's weight will be 7. That's a dramatic difference. So even though both scenarios aggregate two transactions a week apart, the results are very different:

Edited by comment
##### Share on other sites

Thanks this was helpful and really what I wanted. I am looking into whether this is suitable but probably not. However, it has given me the idea that it might be interesting to weight something based on an inflation index...

##### Share on other sites

This topic is 2348 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account

• ### Similar Content

• #### Find by another field containing year

By Sinky,

• 7 replies
• 258 views
• #### Count list of values from Summary list field

By Sinky,

• 9 replies
• 328 views
• #### Need Custom Function to determine a date skipping weekends and bank holidays

By wfcperrine12,

• 3 replies
• 526 views
• #### Days Since Last Race (record)

By Poruchan,

• 665 views
• #### Date calculation in portal (remove ?)

By ash1474,

• 9 replies
• 908 views
×
×
• Create New...