5 Replies Latest reply on Jun 11, 2010 11:06 AM by fitch

Month Calculation Help

Title

Month Calculation Help

Post

Help with calculation would be amazing! Thanks in advance.

3 fields:

Orders:ShipDate

Calendar: DateDisplay

Orders:TotalShipped

Calendar: DateDisplay is of course, on a calendar layout. You can switch from month to month. This is from the SeedCode Calendar.

I'm wanting a calulation to show  the total of orders:totalshipped for only the orders shown on the month (calendar:displaydate) shown.

Currently, it will add all of the orders:total shipped for the entire month shown (even when the days at the beginning and end of the month are in the past/future month)

So, if the month of the orders:shipdate is the same as the month of the calendar:display date then add the orders:total shipped of only the days in that month.

Thanks for the help!!!

• 1. Re: Month Calculation Help

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.

• 2. Re: Month Calculation Help

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?

Oh yeah...

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.

• 3. Re: Month Calculation Help

Fitch,

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.

• 4. Re: Month Calculation Help

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.

• 5. Re: Month Calculation Help

Thanks, Phil -- I should have caught that.