9 Replies Latest reply on Aug 20, 2014 9:39 AM by philmodjunk

    Calculating Rolling 12 Months for charting

    sccardais

      Title

      Calculating Rolling 12 Months for charting

      Post

           We track important company metrics in a file with separate tables going back several years for each metric. (sales, New Customers, free trials, etc.) Each record in these tables contains the date, (year and month), the product and value of the metric. 

           To remove the effect of seasonality, we'd like to chart these on a rolling 12 month basis but I'm not sure how to do this. The datapoint for each month is the sum of 12 records but the group being summed shifts each month. - adding the most recent month and dropping the oldest. 

           For example, assuming we are tracking Sales.

           the RTM (rolling 12 month) value for Jan 2014 would be the sum of Feb 2013 thru Jan 2014. The value for Feb  2014 would be the sum of Mar 2013 thru Feb 2014. And so on.

           We have the individual monthly values but how can we create a chart plotting these RTM values?

            

            

        • 1. Re: Calculating Rolling 12 Months for charting
          philmodjunk

               Perform a find for just the records you want to use to provide data points for your chart.

               You might also be able to specify a date range calculated from the current date to set up a relationship that links to only the desired records.

               And if you set up a delimited data based chart, a calculation using ExecuteSQL could produce delimited data from the same set of records also by calculating criteria from the current date.

          • 2. Re: Calculating Rolling 12 Months for charting
            sccardais

                 Is there a way to calculate the value of a field (Value) for the current record and the previous 11 months?

                 Each record contains

                   
            •           Date (a month and year starting January 2012)
            •      
            •           Metric: a name for the "metric" we are tracking
            •      
            •           Value of the metric for that month / year.
            •      
            •           ReportingPeriod: sequential starting with 1 for Jan 2012 and increasing by 1 for each subsequent month/yr.

                  

                 A new field (c_RTM_Value) could calculate the rolling 12 month value for that record. It would be the sum of "Value" in the current record and the previous 11 months.

                 Using Get(RecordID) and GetNthRecord), I can get the Values for the starting and ending periods but how do I get the months in between?

                  

            • 3. Re: Calculating Rolling 12 Months for charting
              philmodjunk
                   

                        Is there a way to calculate the value of a field (Value) for the current record and the previous 11 months?

                   Yes, you should be able to set up a self join relationship that matches to the records for the preceding 11 months and then you can access the data in the related records to combine with fields in the current record to compute a value.

                   
                          
              •                Date (a month and year starting January 2012)
              •           
              •                Metric: a name for the "metric" we are tracking
              •           
              •                Value of the metric for that month / year.
              •      

                   Is Date an actual date field or a text field that just stores the month and year? A typical way to use a date field for this is to store a date in a date field where the day value is 1--the date of the first day of the specified month, year.

                   If you used such a date field for Date in your record, you could add a calculation field, let's call it cStartDate,  that computes a date 11 months in the past:

                   Date ( Month ( Date ) - 11 ; 1 ; Year ( Date ) )

                   You can then set up a new table occurrence based on the same table in order to set up a self join relationship:

                   YourTable::Date > YourTable 2::Date AND
                   YourTable::cStartDate < YourTable 2::Date And
                   YourTable::Metric = YourTable 2::Metric

                   Then you can use aggregate functions such as Sum or Average to compute values of all 12 records in that date range (includes the current record) for the specified metric. Sum ( YourTable 2::Value ).

              • 4. Re: Calculating Rolling 12 Months for charting
                sccardais

                     How can I restart the "Rolling 12 Month" count when the product changes and include RTM values only if there are at least 12 reporting periods (including current reporting period) for the same product?

                     Each table currently contains the data for one metric. Different metrics are in different tables. (Note: I may change this later but this is how they are structured now.)

                     Each table contains fields for the Reporting Period (monthly starting 1/1/2012) for multiple products. 43 records for each product.

                     Since I'm trying to generate rolling 12 month calcs, the first reporting period is 12/1/2012. So, I want to show values for each Product starting with 12/1/2012. Can the relationship you suggested above be configured to do this? Currently, it is including previous months for all Products. I want to be able to report for all all products but I'd also like the ability to report by Product.

                • 5. Re: Calculating Rolling 12 Months for charting
                  philmodjunk

                       Seems like you have two different problems there, the fact that you use a different table for each metric and the need to match only to records for the correct product. You'll need a slightly different relationship here:

                       YourTable::Date > YourTable 2::Date AND
                       YourTable::cStartDate < YourTable 2::Date And
                       YourTable::ProductID = YourTable 2::ProductID

                       And make sure that Date is of type date and the cStartDate specifies a result type of Date in its specify calculation dialog box.

                  • 6. Re: Calculating Rolling 12 Months for charting
                    sccardais

                         Almost working but not quite. I made the mods above but the field that sums Amount is showing a value even if there aren't 12 full reporting periods for each Product.

                         Assuming the table is sorted on Product and ReportPeriod (ascending), the value in SumAmount should be zero for the first 11 records in each Product grouping.

                         The field containing the Rolling 12 Month value (RTM) sums the previous 11 ReportPeriods plus the current month. 

                         Is there a way to create a calc to do this?

                    • 7. Re: Calculating Rolling 12 Months for charting
                      philmodjunk

                           Count ( RelatedTable::NeverEmptyField )

                           will count the number of related records.

                           You can use this function with an If function to return a calculated value only if the count = 12.

                      • 8. Re: Calculating Rolling 12 Months for charting
                        sccardais

                             Thank you. This worked perfectly but it raised another question about the structure of my database.

                             Currently, the database has 5 tables - one for each metric we are tracking. Before I get too far along with this structure, I'm wondering about the pros and cons of combining these into one table.

                             Would a single table make it easier to generate a report for one product?

                             Would a single table make it easier to compare one metric for a subset of products?

                             Any suggestions about consolidating these 5 tables into one versus keeping separate?

                        • 9. Re: Calculating Rolling 12 Months for charting
                          philmodjunk

                               My first reaction is that this data should all be in one table with an added field to distinguish one metric from another. That allows you some better reporting options that can become difficult to impossible to produce with separate records. And note how the relationships discussed here become much simpler.

                               But much depends on how similar are these different metrics that you are recording, The more similar they are, the better sense it makes to combine them in a single table. The more differences there are, the more likely it becomes that you need separate tables.

                               A Middle ground approach between separate and combined tables is to use one common table with all of the fields that are common to every one of your current separate tables and then you link in your original tables as "detail" records in a one to one relationship and move the data in the "common fields" from the detail tables into the common table. At that point you then delete these fields from the detail tables as their data is now redundant.