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.
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.
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