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

    Month Calculation Help

    mgxdigital

      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
          philmodjunk

          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
            fitch

            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
              philmodjunk

              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
                LaRetta_1

                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
                  fitch

                  Thanks, Phil -- I should have caught that.