1 2 Previous Next 17 Replies Latest reply on Feb 7, 2016 12:52 AM by @ppear

    Calculating Rolling 12 months

    sccardais

      I'm having trouble calculating a value in Table A (MONTHS) that summarizes data in Table B (SURVEYS) if the SurveyDate in SURVEYS falls between 2 dates in MONTHS and if I have at least 12 months of prior data.

       

      • The purpose is to show trends and remove seasonality by showing data in groups of 12 month periods - with each month summarizing the current month and the previous 11 months - sometimes called a Rolling 12 months.

       

      SURVEYS contains about 3,500 individual customer surveys with scores (1-10) Each survey has a SurveyDate converted to show all dates from the 1st day of the month. (e.g. 5/15/2014 is converted to 5/1/2014)  For each month in which I have 11 months of previous data, I want to show the average score from SURVEYS and perform other calcs once I get the relationship working.

       

      To do this, I created a new table MONTHS as shown below.

       

      So far, I haven't been able to create a relationship that does what I want.

       

      How can I relate MONTHS to SURVEYS to match records in SURVEYS with a Survey Date between MONTHS:: c_RTM_Start and MONTHS::RTM_End  IF there are at least 12 months of data in SURVEYS prior to MONTHS::RTM_End.

       

       

      MONTHS
      Recordc_RTM_StartRTM_End
      16/1/20125/1/2013
      27/1/20126/1/2013
      38/1/20127/1/2013
        • 1. Re: Calculating Rolling 12 months
          Mike_Mitchell

          One possible way is with a portal filter.

           

          Case (

               Count ( SURVEYS::pk ) >= 12 )

          )

          • 2. Re: Calculating Rolling 12 months
            Mike_Mitchell

            Correction: There's really no reason for the Case ( ) function. You can just use:

             

                 Count ( SURVEYS::pK ) >= 12

             

            Of course, this may perform poorly once you get a lot of records in the table, so you may want to look at other methods. (Ex: ExecuteSQL, etc.)

            • 3. Re: Calculating Rolling 12 months
              sccardais

              Thanks, Mike but there could be hundreds of Surveys dated between the RTM Start and RTM End

               

              SCC

              • 4. Re: Calculating Rolling 12 months
                Mike_Mitchell

                Well, in that case (I thought we were dealing with months), you could do this:

                 

                Create a new relationship using a global date field, with a <= predicate to the Date in the SURVEYS table. Set that date to RTM Start. If there's at least one record in that relationship, then you have data going back to the RTM Start date.

                • 5. Re: Calculating Rolling 12 months
                  realgrouchy

                  You need to relate the two tables by Survey Date ≥ RTM_Start and Survey Date ≤ RTM_End.

                   

                  Then you just need to have summary records in the Survey table that can be called by the Months table. From there you can manipulate at will in either the Surveys or Months table.

                   

                  I've attached an example database where the Months table has a calculation to divide SurveyTotal by SurveyCount.

                   

                  - RG>

                  • 6. Re: Calculating Rolling 12 months
                    sccardais

                    But from which context / table?

                     

                    I currently have this relationship.

                     

                    MONTHS::RTM_End_Date ≥ SURVEYS::SurveyDate

                    AND

                    MONTHS::RTM_Start_Date ≤  SURVEYS::SurveyDate

                     

                     

                    MONTHS::RTM_End_Date is a date field in the form of mm/01/yyyy.

                    MONTHS_Start_Date is a calc subtracting 11 months from RTM_End_Date

                     

                    This isn’t working. It’s matching records in SURVEYS with a SurveyDate greater than the RTM

                    • 7. Re: Calculating Rolling 12 months
                      Mike_Mitchell

                      Is MONTHS_Start_Date a date (i.e., is the result of the calculation a date)?

                      • 8. Re: Calculating Rolling 12 months
                        sccardais

                        Yes. MONTHS::Start_Date is a calculation that subtracts 11 months from MONTHS_End_Date. Here’s the calc — Date (Month(RTM_End)-11 ; 1 ; Year(RTM_End) ) The result is set to DATE.

                         

                        MONTHS is simply a table of dates one month apart starting with 5/1/2013. This date is stored in MONTHS::End_Date

                         

                         

                        MONTHS is related to SURVEYS with the following

                         

                        MONTHS::End_Date ≥SURVEYS::Survey_Date

                        and

                        MONTHS::Start_Date ≤ SURVEYS:Survey_Date

                         

                         

                        I want to create a relationship that finds all of the Surveys between a date range. I’ve assumed that I need to do this from a dedicated table (MONTHS) in order to create a column chart showing Months on the x axis and average scores on the Y.

                         

                        Thanks.

                        • 9. Re: Calculating Rolling 12 months
                          Mike_Mitchell

                          I'm still missing why the portal filter I gave you (the second one) wouldn't work ...

                          • 10. Re: Calculating Rolling 12 months
                            sccardais

                            I may be missing it too.

                             

                            If I get the right relationship set up from MONTHS to SURVEYS, in some months, there could be hundreds of Surveys with a Survey Date between the date ranges in MONTHS.

                             

                            Foe example, assume the following

                             

                            MONTHS::EndDate = 7/1/2015

                            MONTHS::c_RTMStartDate = 8/1/2014

                             

                            In SURVEYS, there could be hundreds of individual surveys with dates that fall into this range so I’m not getting how the filter —  Count ( SURVEYS::pK ) >= 12  —  would work.

                             

                            What is the >= 12 comparing?

                             

                            Just to be clear, the end result is a column chart with Months along the x axis and average score along the y axis. Assume one of the columns is labeled July 2015. That column will show the average scores from all of the surveys from Aug 2014 - July 2015. The next column would be Aug 2015. It would plot the average of all Surveys between Sept 2014 - Aug 2015. So, each column represent 12 months data.

                             

                            Does that help?

                             

                            Thanks for giving this so much time / thought.

                             

                            • 11. Re: Calculating Rolling 12 months
                              @ppear

                              I was a little curious and did a test. Thought it was a full 12 months, and then I counted (Year-1).

                              It is possible, I do not understand the problem here. :-)

                              BTW. U have to set the date format before test. I have EU format: YYYYMMDD. I have not test the US yet.

                               

                              This i calculate two related value from a input date:

                              1: From date= Input Date- 1 year

                              2: To date = Input Date

                               

                              Then those are related to Date in DATA.

                              Meny::From date =< DATA::Date

                              Meny::To date => DATA::Date

                              Example input:

                              01/01/2016 = 01/01/2015-01/01/2016

                               

                              I did a "Let" function on "From Date" like this. Because I did not know what date format U have I put in a list function:

                              Let (

                              [

                                    Sep = "-" ;

                                   XYear = Year ( DateIn ) - 1 ;

                                    XMonth = Month ( DateIn ) ;

                                    XDay = Day ( DateIn ) ;

                                    USFormat = XMonth & Sep & XDay & Sep & XYear ;

                                    EUFormat = XYear & Sep & XMonth & Sep & XDay ;

                                    DateOut = Case ( DateFormat = "US Format" ; USFormat ; EUFormat )

                               

                               

                              ];

                              DateOut

                              )

                               

                              /H

                              • 12. Re: Calculating Rolling 12 months
                                sccardais

                                Well - maybe. Could also be that I’m complicating things. Wouldn’t be the first time.

                                 

                                Assume two tables - Months and Surveys.

                                 

                                MONTHS has 48 records

                                           

                                Rec #     RTM_End     RTM_Start

                                1          6/1/14          7/1/13

                                2          7/2/14          8/2/13

                                3          8/2/14          9/2/13

                                     break

                                48          5/28/18          6/28/17

                                 

                                SURVEYS has about 4,000 records and more are added every month.

                                 

                                Each record is a Survey with a date and a score.

                                 

                                I want a column chart showing a trend in average scores from Surveys. I’m assuming it would have to come from MONTHS by a relationship with SURVEYS.

                                 

                                For each record in MONTHS, I want the average of scores from SURVEYS for if the Survey Date is between MONTHS::RTM_Start and MONTHS::RTM_End — IF there are at least 12 months of data in Surveys prior to MONTHS::RTM_End.

                                 

                                If I run this manually, searching SURVEYS::SurveyDate for a range between 8/1/2013 … 7/1/2014, I find 938 Surveys with an average of 7.57. When I advance one month (9/1/2013 -8/1/2014), I get 980 Surveys and and avg goes to 7.97. And so on …

                                 

                                I could do this manually (obviously) but I would also like to do the same calcs for different versions of our product. We have 9 so that would require quite a few manual calcs and I just KNOW FMP can do this!

                                 

                                Thanks.

                                • 13. Re: Calculating Rolling 12 months
                                  sccardais

                                  I read your post in my email client and didn't see the attachment or much of your content so my response below may not seem responsive. I'll look at your test file now. Thanks

                                  • 14. Re: Calculating Rolling 12 months
                                    Mike_Mitchell

                                    You need a second relationship. (I should have said "relationship", not "filter" before.) You have to be able to determine whether there are 12 months' previous data, and you can't do that with the relationship you have.

                                     

                                    I'm also not 100% certain what you're looking for when you say, "12 months of data". Do you mean:

                                     

                                    1) there are data in every one of the preceding 12 months?

                                    2) there are data in the start date - 12 months?

                                     

                                    Those are two different questions. If it's the latter, then all you have to do is set a date field = Start - 1 and build a relationship on that. OTOH, if it's the former, then you need to isolate whether there is data in each of the preceding 12 months. That would be more complicated, but still manageable.

                                    1 2 Previous Next