Have you seen this date calculation?
DateField - Day ( DateField ) + 1
Put that in a calculation field set to return a result of date and it will return a date that is the first day of the month for all dates in DateField that are within that month.
You can sort on this field in a sub-summary report to group your records by month and year. You can also use this field in a relationship and/or a portal filter to link to all records of a specific month so that you can use a Sum() function (relationship) or with a summary field (relationship or filemaker 11 portal filter) to compute monthly totals.
Interesting. When I need the first day of a date's month, my habit is to use:
Date( Month(DateField) ; 1 ; Year(DateField) )
For last day of the month I use:
Date( Month(DateField) + 1 ; 0 ; Year(DateField) )
What was the question?
Maybe you could have a calc:
Case(Month(ShipDate) = Month(DateDisplay);TotalShipped)
And you could add up that field.
Or you could make the calc return a boolean:
Month(ShipDate) = Month(DateDisplay)
And you could add up the related TotalShipped.
FWIW: looking at the free SeedCode calendar, the DateDisplay field I see is DateDisplayCalc and it's just text; the actual date comes from a field called DateFocusGlob.
Yeah Date( Month(DateField) ; 1 ; Year(DateField) ) and Date ( DateField ) - Day(DateField) + 1 produce the same date I've used one about as often as the other in fact.
Your calculation, however: Case(Month(ShipDate) = Month(DateDisplay);TotalShipped)
would cause items shipped in January 2009 and January 2010 to both return the total shipped if DateDisplay is a January date. You might want to include a comparison of the year as well as the month or use one of the two date calculations for the first of the month to avoid that problem.
Yeah, Comment's coming up with:
DateField - Day ( DateField ) ... producing the last day of the prior month and
DateField - Day ( DateField ) + 1 ... producing the first day of the current month
... make me smile whenever I use them.
Thanks, Phil -- I should have caught that.