2 Replies Latest reply on Mar 21, 2011 11:08 AM by PJSpark

    return all totals for each month



      return all totals for each month



      I haven't been able to achieve getting a total or average of all amounts of each month.

      What I would like to report is:

      January 15.50 (22.00)

      February 75.67 (70.23)


      where the first number is the sum of all transactions for a given month in a year (I've done that), and the second number is a total, or actually an average of all January's etc. of all years, such that it is easy to see if you are higher or lower than average. It would also be easy enough to make it into a percentage. I thought I could use a self-join:

      Main Table-----X----- Main.All Main.Month


      So, .All would return all records regardless of found set, and month would return just the totals for the month. But since it returns the same number all the time, I need a way to specify what the current month is on the report.

      The report is based on the Main Table and is generally filtered to show just one year at a time. All seems to work except tallying up the all-time amounts. 

      Hope this makes sense, and I've given enough info.



        • 1. Re: return all totals for each month

          MainTable::Month = MainTableSameMonth::Month

          MainTableSamemonth is a second table occurrence of MainTable.

          Define a calculation field in MainTable as:

          Average ( MainTableSameMonth::FieldTobeAveraged )

          This assumes that the month field is either the month number or the month name. Either should work here. You can't use my personal favorite (learned from a post by Comment) for a month calculation: DateField - Day (DateField) + 1 as this produces a date for the first day of each month and that value will be different for each year.

          • 2. Re: return all totals for each month


            I was really racking my brain over this, and you come up with an answer as easy as pie. It works, even with filtering the report!

            Thanks again