Sub Summary Report to Create Running Totals

[sccardais]

I'm trying to create a sub summary report to track trends in Customer Satisfaction Surveys.

 

As Surveys are received, each is given a classification as Promoter, Passive or Detractor. A calculation field based on a Summary field calculates a "Net Promoter Score" by subtracting the % of Detractors from % of Promoters.

 

A report summarizes the results by month but it restarts the calculation with each change in the break field c_Survey_YYYY_Mo. 

 

I would like each new month to show the result for the current month and all previous months. 

 

 A screenshot showing part of this report is attached.

 

All of the data to do this should come from a single file (Surveys).

 

I want each change in the break field (c_Survey_YYYY_Mo) to show a "running total." I've been trying to figure out how to do this for weeks and simply cannot get my mind around the logic. Any help would be appreciated.

 

 

[comment]

The description is not quite clear: does the summarized table actually have 178 records for the month of May 2013, 98 records for the month of June 2013, and so on?

[Matthew F]

It looks to me like each of the rows that you show in your graphic ought to be a sub summary part when sorted by "Month".  To facilitate this, you will want to have a calculated field "Month" set to Month ( Survey Date ).  You can then generate some boolean calculated fields (e.g.  calcPromoter...  Classification = "Promoter". Finally, define some summary fields set to display totals of the boolean fields and place these on the sub summary.    (See attached)

 

survey.fmp12.zip

 

[sccardais]

The description is not quite clear: does the summarized table actually have 178 records for the month of May 2013, 98 records for the month of June 2013, and so on?

COMMENT:

 

Yes. There were 178 surveys in May, 98 in June, 2 in July, etc. These numbers are accurate but they don't show a trend.

 

I would like each month to be the total of the current month plus all prior months. So, the Surveys Received column should be relabeled to something like, Cumulative Surveys Received and the value for June 2013 should be 276 (178 + 98). The value for July should be 278, etc.

 

Same for the next three columns, Promoters, Passive and Detractors. The Net Promoter Score and NPS Average columns should be based on the cumulative data.

[sccardais]

It looks to me like each of the rows that you show in your graphic ought to be a sub summary part when sorted by "Month".  To facilitate this, you will want to have a calculated field "Month" set to Month ( Survey Date ).  You can then generate some boolean calculated fields (e.g.  calcPromoter...  Classification = "Promoter". Finally, define some summary fields set to display totals of the boolean fields and place these on the sub summary.    (See attached)

 

survey.fmp12.zip

Matthew: 

 

Thanks very much for including the sample file. The technique you describe and the sub summary report generated in your sample file accurately shows the count for each month but doesn't show the cumulative results for the current and previous months.

 

I finally found a way to generate the report that I wanted after posting this topic last night. Funny how the process of describing the problem so often helps solve the problem!

 

The process I used is so convoluted that I am CERTAIN there must be a more elegant approach. If I don't fully document the steps in generating this Monthly Trend Report now, I will never be able to understand it.

 

So, for the next few minutes, I'm going to write something that describes the process and post it later on this thread for criticism (!) and comment.

 

Thanks again for taking the time to create the sample file.

 

From the mountains of Western North Carolina .. Scott C.

[comment]

See if this helps:

 

RunningCounts.fp7.zip