5 Replies Latest reply on Jan 13, 2010 6:08 PM by user14360

    Weekly / daily sales tables

    user14360

      Title

      Weekly / daily sales tables

      Post

      Hi.  Am having a crisis in confidence over this issue.  (Filemaker Pro 10 Advanced, Mac OS X.)

       

      A client wants to switch from only tracking their sales data for different retailers on a weekly basis (one figure per retailer per week), to being able to also do it on a daily basis, if the data is available.

       

      I have two tables: Weekly Sales and Daily Sales.  They are related by the Week of Quarter field (so in the Weekly sales table, one record is Week 1 of Q3, and in the daily sales table, all the records that are in week 1 of Q3 will be related.)

       

      There is a field in the Weekly Sales table that allows you to manually input a weekly sales figure, but then there is a calculation field (and this is the one that everyone sees) that says "if there is something related in the daily sales table to this particular week, then use the sum of that data here in this field.  Otherwise, use the data from the manual field in the Weekly Sales table."

       

      This is working fine so far.

       

      My crisis in confidence lies in the question of multiple records for sales of different products in the daily table.  So for example, on the 26/12/09, perhaps $500 of Product 1 was sold and $700 of Product 2 were sold.

       

      My instinct says they should be two separate records, each with the date 26/12/09, but the client is wondering why they shouldn't have just one record for 26/12/09 with multiple fields for each product.

       

      I think they should be separate records to allow for new products, changes in products, probably better reporting etc...

       

      But, I want to be able to drill down into the detail, from the topline Week figure, to the total Day sales figure and then into the details of exactly how much was sold of each product.

       

      So my two main  questions are:

      1) Am I right in thinking each record in the Daily sales table should be for just 1 date and 1 product?

      2) Do I actually need 3 tables?  Weekly sales (with a field summing the daily sales), Daily Sales (with a field summing the product sales), and Product Sales?

       

      Or, can I just have the weekly sales and then the Daily Sales, and have a self-join relationship in the Daily Sales that allows me to sum up the total of the product sales for that day?  This is what I've done now, but am not sure how to view in a portal ONLY 1 instance of each daily date, so that I can click on that and bring up the details of the sales of each different product in a different table.

       

      So I thought I'd post and see if I'm on the right track in my thinking.  Basically, it's going to be a pain for the client (and will cost them some money too) to change over their sales reporting structure to what I'm suggesting, and before they go ahead and do that, I wanted to make sure I'm right in my thinking!

       

      Sorry about the long message and thanks for any input. 

       

       

        • 1. Re: Weekly / daily sales tables
          comment_1
            

          Hildy wrote:
          Am I right in thinking each record in the Daily sales table should be for just 1 date and 1 product?

          Most definitely.

           

           

           


          Hildy wrote:
          can I just have the weekly sales and then the Daily Sales

           

           Yes. And you don't need a self-join: just produce a report summarized by date and by product (with no body part).

          • 2. Re: Weekly / daily sales tables
            user14360
              

            Thanks Comment.  Although the subsummary reports are very powerful, the user wants to be able to click on a weekly sales date, and see the totals of the sales for each day in that week (but only 1 record per day), and then be able to click on the day and be able to see the specific sales of each specific product for that day.

             

            I don't know of any other way to do this in a subsummary report, except for having a few different report layouts and writing scripts so that when you land on a report, you first only see the weekly sales data relevant to that retailer and that quarter, and then when you click on a date it takes you to the other report that shows you the summary of each day sales in that week (so, with no body part), and then when you click on a day it takes you to another report which shows the detail of the products sold that day (with the body part).  This seems unwieldy and will also be more limited in formatting.  (Please enlighten me if there is another way!)

             

            I'd like to try to do it with a portal from the main retailer, if I can. 

             

            What I want to have is:

             

            Main layout based on Retailer info.

            Filtered portal into the weekly sales data for the quarter currently selected.  When you click on a week date, you see in the next filtered portal:

            A list of dates from that week, with the total sales for each day. When you click on a day date, you see in the next filtered portal:

            A list of all the products sold on that particular day and the amounts.

             

            Is there a way to achieve this without having to build an interim "Daily Dates" table which contains just one instance of each date?

             

             

            • 3. Re: Weekly / daily sales tables
              user14360
                 No - I take it back.  I'm sure I can accomplish what I need to with the subsummary report.  Thanks again.
              • 4. Re: Weekly / daily sales tables
                comment_1
                  

                Hildy wrote:

                Is there a way to achieve this without having to build an interim "Daily Dates" table which contains just one instance of each date?


                Possibly, but that WOULD be wieldy indeed, while the summaries should be a breeze, IMHO. I believe you only need two layouts (one with no body for the weekly/daily summaries, and one for the detailed report) and a few scripts. Keep in mind that a sub-summary shows only when the breakfield is in the sort order.

                Also I don't see why such a report would be "more limited in formatting" than a portal.


                • 5. Re: Weekly / daily sales tables
                  user14360
                     I may find indeed it's not more limiting in formatting, with some more experimentation.  I have just found a bit that the vertical "down-the-page" nature of the subsummary reports less flexible than using portals on a form, but I'll keep playing.  I think it will work for me here.