5 Replies Latest reply on Dec 4, 2013 11:05 AM by philmodjunk

    52-week moving average without fixed entry count

    FredrikWallenberg5579

      Title

      52-week moving average without fixed entry count

      Post

           I have all our sales data one line per line item (multiple lines per transactions). I'd like to create a moving weekly average (52 weeks) to give a better view of trends letting the moving average "correct" for seasonality. Because of the data structure I can't count entries (I don't know how many entries go into each week). Is there anyway to do this in Filemaker? I'd essentially need to have each graph entry be the average (or sum) of all sales that fall between two rolling dates. I'm not even sure how to create a new table with that type of information.

           I know Filemaker isn't a statistics program, but having some more stats features would be nice :-)

        • 1. Re: 52-week moving average without fixed entry count
          philmodjunk

               What you describe sounds possible. But I think you left out a few details. Until I got to this sentence:

               

                    I'd essentially need to have each graph entry be the average (or sum) of all sales that fall between two rolling dates.

               I didn't even know you wanted to chart the data. Since charting an average for the most recent 52 weeks of data would only produce a single data point on your chart, there appear to be some details missing. Are you charting averages for different products or groups of products? Is each point on the chart a different average for a different 52 week interval?

          • 2. Re: 52-week moving average without fixed entry count
            FredrikWallenberg5579

                 Phil, do you ever leave these boards? Thankfully it seems the answer is no :-) 

                 Let's just simplify and say I want total sales (all products) for the last 52-weeks on a weekly basis (week 1 this year would include all sales starting week 2 last year, week 2 this year will have total sales since week 3 last year etc etc). Thus, one point per week (but each point shares data with another 102 points, 51 on each side of it). Does that make sense?

            • 3. Re: 52-week moving average without fixed entry count
              philmodjunk

                   I'm not sure I follow that. See if this agrees with what you have in mind:

                   point 1: total for 52 weeks of data starting with week 3 data of this year but including data from all data from the preceding 52 weeks
                   point 2: total for 52 weeks but starting with week 3
                   Point 3: same typ total but from week 4

                   That it?

                   If so then it would make sense to set up a table of one record per week that you link to your related table of data.

                   You could, for example, define cWeek in your current data table as a calculation field that returns the date for Sunday of the same week: DateField - DayOfweek ( DateField ) + 1

                   Then, if you create  a record for each week that you want to chart with a date for that same Sunday date, you can set up a calculation field that stores 52 Sunday Dates in a field to form a Return Separated list of dates. There are custom functions that can produce that list or you can use a looping script.

                   Then this relationship:

                   WeeksTable::cSundayList = YourDataTable::cWeek

                   will match to all records for that same week plus the preceeding 51 weeks. Summary fields defined in YourDataTable can return totals, averages, etc based on that 52 week time period for charting on a chart set up on the WeeksTable.

              • 4. Re: 52-week moving average without fixed entry count
                FredrikWallenberg5579

                     Ah, the list ... that was the "missing link". I have year-week right now but would just change that to a serial number. Then, as you say, I can easily produce a list field with 52 entries separated by comma (or whatever). Will your relationship return any entry where one of the list items in cSundayList matches cWeek?

                • 5. Re: 52-week moving average without fixed entry count
                  philmodjunk

                       The list must have values separated by a return. It won't work with a comma delimiter.

                       

                            Will your relationship return any entry where one of the list items in cSundayList matches cWeek?

                       Yes.