Jump to content

Weighting summaries by date


This topic is 2991 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?

Link to comment
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?

 

Link to comment
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?

Link to comment
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:

2016.png.f2d4f4bd407c079c8b7c00a130471db

0001.png.74bca0141904370edecd79d5c05fe19

 

Edited by comment
Link to 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...

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.