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

    Month Calculation Help



      Month Calculation Help


      Help with calculation would be amazing! Thanks in advance.


      3 fields:


      Calendar: DateDisplay



      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



              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.