1 Reply Latest reply on May 27, 2015 9:36 AM by philmodjunk

    12 month rolling total routine

    Stu412

      Title

      12 month rolling total routine

      Post

      I'm looking at building a 12 month rolling total routine with a table which I think makes sense.  It would look something like this:

      CustomerID | Date | cMonth | Value | CRolling12MonthSummary

      Each date would be the first of the month, the cMonth would pull the month (Jan, Feb, Mar) from the date field and the value would be entered manually or uploaded.

      How do I get this to do rolling 12 month summaries based on the value?  Obviously the first 11 rows of cRolling12MonthSummary are empty, but how do I set the 12 month range which would move +1 for each successive summary row etc?

      Thanks in advance

       

        • 1. Re: 12 month rolling total routine
          philmodjunk

          Don't see a need for cMonth. You can use data formatting to just show the month name from Date. CRolling12MonthSummary can be a field of type summary that computes the total of Value.

          You can perform a find for all records from the current month back to first of the month 12 months ago and sort the found set by Date to group them by month. (You did say that date always stored a date for the first of the month...)

          A sub summary layout part can contain the summary field to show the total for that month. You can put the Value field in the body of the layout if you want to list the values used to compute each month or you can remove the body layout part and just have 12 rows of data (once you have records that span all 12 months), with one total for each month.