5 Replies Latest reply on Apr 23, 2010 1:10 PM by comment_1

    Averaging Last 4 Weeks

    GeorgeL

      Title

      Averaging Last 4 Weeks

      Post

      Hi I'm pretty new to FM and wanted to know how to build a database properly in which I can import a weekly sales report and have a calculation for average units sold per week for the last 4 weeks.

       

      I have two tables right now, one as the Product table with the UPC related to a Weekly_Sales table. As of now I'm using date codes by week (01-52) and the year as a separate field. I did this in order to find out sales trends by week. I also have a GLOBAL date code for the current date so it tags all the newly imported data as that week and year.

       

      I'm having trouble finding the average for the last 4 weeks of units sold from the Weekly_Sales table automatically; especially if the its Week 01 Year 2010 I'm not sure how to get it to automatically calculate data from Week 49 Year 2009.

       

      Let me know if I'm approaching this correctly and I'm open to other ways to approach this.

        • 1. Re: Averaging Last 4 Weeks
          philmodjunk

          I learned much of this from a post by Comment:

           

          For starters, use this calculation on an actual date to record the week instead of two fields, one for the week and one for the year:

           

          DateField - DayOfWeek(DateFIeld) + 1

           

          This will take any date and return the date of the first day of that week (Sunday). This approach will allow your calcs to refer to weeks from the previous year.

           

          IF the above calculation is a field called cWeek,

           

          cWeek-21 will return the Sunday date 3 weeks earlier.

           

          Let's call the field c4Week. Now you can make a second table occurrence of Weekly_Sales and link them as:

           

          Weekly_Sales::c4Week < Weekly_Sales 2::cWeek AND

          Weekly_Sales::cWeek > Weekly_Sales 2::cWeek

           

          Now a calculation, Average ( Weekly_Sales 2::Units Sold) should give you the 4 week average.

          • 2. Re: Averaging Last 4 Weeks
            GeorgeL

            Thanks for getting back to me. Everything works great for one product being sold so when I implemented this for multiple products I came up with two issues.

             

            First because I have different products sold so I had my UPC in the Products table related to the Weekly_Sales table. When I did the calculation for the Average it would give me the Average for all the units sold for every product. I need the average for each individual product. I then I tried to relate the UPC from Products to Weekly_Sales 2 which gave me an Average for each product but all the information from related from the Products table do not show up.

             

            Second issue I came across is that if I did the Average calculation from Weekly_Sales 2 in the Products table it would give me the average of all the weeks.

             

            I'm sure somehow the two issues are somehow related.

             

            Thanks again,

            George

            • 3. Re: Averaging Last 4 Weeks
              comment_1

              You should start by finding Weekly_Sales records in the last 4 weeks. Then sort them by UPC, and use a summary field in a sub-summary by UPC part to show the average figure (you don't need a body part for this report).

               

              Note that the report is produced from the Weekly_Sales table.

              • 4. Re: Averaging Last 4 Weeks
                GeorgeL

                Cause the data I was importing was from our retailer, the weeks given were based on the retailer's dates. The data given was as follows: 201011 - which meant year 2010 week 11. I figured out a way to calculate 4 weeks ago date based on a 52 week calendar (e.g. if the date was 201003 the 4 weeks ago return should be 200951).

                 

                So if

                cWeek = 201004

                c4Week = If ( (cWeek / 100) - .04  ≤ Floor ( cWeek/100 ) ; cWeek - 52 ; cWeek - 4 )

                then the return should be

                c4Week = 200952

                 

                Then you can follow the averaging function Phil has shown. Hope this helps anyone else with this problem.

                • 5. Re: Averaging Last 4 Weeks
                  comment_1

                   


                  GeorgeL wrote:

                  The data given was as follows: 201011 - which meant year 2010 week 11.


                   

                  That's not sufficient. There are several methods to number weeks; some assign up to 54 weeks to a year, some 53. You need to find out the exact method being used. One thing is certain: no method that has only 52 weeks in a year can last for long.