3 Replies Latest reply on Aug 9, 2013 9:46 AM by philmodjunk

    Calculate Weekly and Monthly Sales



      Calculate Weekly and Monthly Sales


           I have a table called payment that has the following fields:-

           PaymentId - Number

           Date - Date

           Amount - Number

           InvoiceId - Number

           EmployeeId- Number

           PaymentTotal - Summary( Total Of Amount(running with restart), when sorted by employeeId)


            have created a report which basically shows the weekly sales total of each employee. How can I do where it shows the weekly amount total and monthly amount total.




        • 1. Re: Calculate Weekly and Monthly Sales

               Use a non running summary field and place it in sub summary parts "when sorted by cWeek" and "when sorted by cMonth".

               cMonth can be a calculation field set to return a date with this calculation: Date - Day ( Date ) + 1. It returns the date of the first day of the month for all dates in the date field in that same month.

               cWeek will depend on your definition of a "week". A simple option is WeekOfYear ( Date ), but this may not produce the result you want for the first and last weeks of the year that can start in one year and end in the next year. Another option is: Date - DayOfWeek ( Date ) + 1 -- which returns the date for Sunday of the same week.

               WIth both sub summary layout parts, you can put the non-running summary field inside the sub summary parts to show the weekly and monthly totals. If you sort your records in an order that includes cMonth in the sort order, the first sub summary part will be visible. If you include cWeek, the second sub summary part will be visible and if you include both, both will be visible.

               Thus, you can get a number of different views of your data with different computed subtotals just by how you choose to sort your records.

          • 2. Re: Calculate Weekly and Monthly Sales

                 Thank you but I dont quite understand. Copuld you give me an example with some records.


                 Also my boss wants me to create a report that shows the weekly sales by each employee and also the yearly sales by each employee. He doesnt want the monthly sale to be shown.


                 Out week starts on friday and ends on thursday.


                 My idea was to search records for the current year from this table. That will give me the yearly total for each employee . But how do I make it work so it shows the weekly too.




            • 3. Re: Calculate Weekly and Monthly Sales

                   What you want to do matches what I have suggested. You perform a find to find either all records for a given time period such as the current year and either for a single employee or all employees. THen sort your records to get the needed totals and sub totals.

                   Here's a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial