13 Replies Latest reply on Apr 22, 2014 9:51 AM by sccardais

    Summarizing Year To Date

    sccardais

      Title

      Summarizing Year To Date

      Post

           The screenshot below shows a portion of a sub summary report that shows results of customer satisfaction surveys by month. Because the number of surveys received varies so widely from month to month, the report doesn't depict trends properly.

           Instead of reporting results for each month, it should report aggregate numbers for the current month AND all preceding months. e.g. June 2013 should include June and May 2013. July 2013 should include July, June and May. etc.

           I'm totally lost about how to generate a report that totals YTD results. Any suggestions would be very, very welcome.

      YTD_Summary.jpg

        • 1. Re: Summarizing Year To Date
          philmodjunk

               For YTD results, the most straight forward approach is to use a relationship that matches by a field that only has the year for the relevant date. That will then allow you to get a total for all records of the current year instead of just for one month of that year.

          • 2. Re: Summarizing Year To Date
            sccardais

                 Not sure I understand how that would give me what I want.

                 Referencing the screenshot from the initial post .. data in the row labeled 2013-06 should be the sum of data for 2013-06 and 2013-05. The calcs in the 2 rightmost columns should be based on June and May.

                 Likewise, July's data should be the sum of May, June and July. And so on.

                 The idea is to show the trend over time.

                 Am I misunderstanding your solution?

                  

            • 3. Re: Summarizing Year To Date
              sccardais

                   Is this a situation where a Running Total summary might be appropriate? 

                   Create a Summary / Running Total field that totals the count of surveys classified as Promoters. Use this new field in a sub summary report using the Year-Mo at the break field?

                    

              • 4. Re: Summarizing Year To Date
                philmodjunk

                     I was misunderstanding what you needed.

                     A relationship with an inequality could do what you need. You can match to all records where the month year is > 2013-01 and < the month-year of that record or group of records in your found set.

                     The "January of same year" value can be set up as a calculation field so that you have these match fields:

                     LayoutTable::cJanuarySameYear < RelatedTable::MonthYear AND
                     LayoutTable::MonthYear > RelatedTable::MonthYear

                     Additional match fields might be included if you need to match to all YTD records that are of a given category.

                • 5. Re: Summarizing Year To Date
                  sccardais

                        I might be misunderstanding your suggestion.

                       The calcs and relationships you suggest seem to be narrowing down the dates to a date range.

                       I'm wondering how to show the YTD values for each row in the report so that each row (each new month) is the cumulative result of the current month and all previous months.

                       Is that what you're thinking and suggesting? 

                  • 6. Re: Summarizing Year To Date
                    philmodjunk
                         

                              The calcs and relationships you suggest seem to be narrowing down the dates to a date range.

                         Correct. And that date range will be different for each row in your report and thus a summary field or aggregate function calculation based on that relationship will show a total based on all records of the same year up to the month year of that row in your report.

                    • 7. Re: Summarizing Year To Date
                      sccardais

                           Phil:

                           I have no experience with this type of relationship but I'd like to learn. Can you suggest where I might look to see examples or learn how these relationships work?

                           A relationship with an inequality could do what you need. You can match to all records where the month year is > 2013-01 and < the month-year of that record or group of records in your found set.

                           The "January of same year" value can be set up as a calculation field so that you have these match fields:

                           LayoutTable::cJanuarySameYear < RelatedTable::MonthYear AND
                           LayoutTable::MonthYear > RelatedTable::MonthYear

                            
                      • 8. Re: Summarizing Year To Date
                        philmodjunk

                             The relationship details have just been quoted by you in your last post. After you drag from a field in table occurrence 1 to table occurrence 2 to start the relationship, double click the relationship line to open a dialog where you can specify additional relationship details such as adding an extra pair of match fields and changing the relationship operator from the default = to some other operator such as > or <.

                             The calculation field, cJanuarySameYear can be as simple as:

                             Year ( Datefield ) & "-01"

                              

                        • 9. Re: Summarizing Year To Date
                          sccardais

                               I have read and re-read the instructions above and I'm not understanding. Please bear with me.

                               I created a calc field in Surveys c_JanSameYear. Year(c_SurveyDate). Result is "text"

                               I created a new self join TO based on Surveys. Surveys_YTD 

                               Is this the structure you are suggesting?

                               The layout for the report would be identical to the one in the first posting except that it would be based on the new TO?

                                

                                

                          • 10. Re: Summarizing Year To Date
                            philmodjunk

                                 The lower image does not match the upper image where we should also see the c_JanSameYear field in the Surveys table occurrence.

                                 You would not change the TO for your layout. This added TO is to be referenced by a calculation field to compute a total for all records from January of the same year to the month/year specified in c_SurveyYearMO.

                            • 11. Re: Summarizing Year To Date
                              sccardais

                                   Still working on making this YTD work. 

                                   I changed the relationship as you suggested. Screenshot attached. Does this look correct now?

                                   Created calc field in Surveys c_JanSameYear.  Year ( c_Survey_YYYY_Mo ) & "-01"

                                   The original layout was based on Surveys. Should that stay the same or change to Surveys_YTD?

                                   The original sub summary was based on Surveys: c_Survey_YYYY_Mo. Should this be changed to Surveys_YTD or do I need a different break field?

                                   Can you elaborate on this comment from your earlier post? "This added TO is to be referenced by a calculation field to compute a total for all records from January of the same year to the month/year specified in c_SurveyYearMO.

                                   I'm assuming the added TO is Surveys_YTD. 

                                   What calculation field are you referring to?

                                   Will the technique you are suggesting work across multiple years? Our data starts in May 2013 and I would like the report to show trends starting in May 2013 and monthly thereafter.

                                   Once I get this monthly trend report working, I'll do a similar report for a Quarterly timeframe.

                                   Thanks again for you help and patience.

                              • 12. Re: Summarizing Year To Date
                                philmodjunk

                                     That's not what I recommended. In your previous screen shot. The lower image was correct. But what was shown in the lower image was inconsistent with the upper image.

                                     Assuming that Surveys is the TO specified for your layout, c_Survey_YYYY_MO should be the only match field in Surveys_YTD. c_JanSameYear and c_Survey_YYYY_MO should be shown as match fields in Surveys.

                                • 13. Re: Summarizing Year To Date
                                  sccardais

                                       Thank you.

                                       I found another way to do what I want using several new calc fields based on "running totals" but I will try the method you describe as well.

                                       I really appreciate your continued help.