8 Replies Latest reply on Mar 5, 2014 8:39 AM by sccardais

    Calculations in SubSummary Report

    sccardais

      Title

      Calculations in SubSummary Report

      Post

           I'm having trouble using Summary fields in a Sub-summary report formatted as a pivot / cross tab table displaying records from a Surveys table using a break field of Product_name.

           Column 1 = Product_name. From Surveys table

           Column 2 = Count of Surveys received from customers using each product category. This is a calculation field in the Surveys table.Count_Surveys

           Column 3: Promoters. A summary field from Surveys table. Total of calculation field, c_Promoters in Survey table. c_Promoters value is 1 if score given by customer is 9 or 10

           Column 4: Passives: A summary field from Surveys table. Total of calculation field, c_Promoters in Survey table. c_Promoters value is 1 if score given by customer is 7 or 8

           Column 5: Detractors: A summary field from Surveys table. Total of calculation field, c_Promoters in Survey table. c_Promoters value is 1 if score given by customer is 6 or less

           Column 6: c_Net_Promoter_Score: a calculation field in Surveys using GetSummary function to calculate the Net Promoter Score for each row (Product Name) using the following formula. Number of Promoters / total Surveys received for this product less Number or Detractors / total Surveys received for this product.

           The calculation for c_Net_Promoter_Score (that is not working) is: (GetSummary(Summary zPromoter;Product)/Count_Surveys)-(GetSummary(Summary zDetractor;Product)/Count_Surveys)

           All of the other fields are displaying correctly - even the Count_Surveys field in column 2. Even though the Count_Surveys for each row is accurate, 350 in the attached screenshot, the formula is using the grand total for all surveys (828) rather than the count for each product.
            
           The correct result of the calculation should be 0.1257 (138/350) - (94/350). The incorrect result is 0.531 because the calculation is using a value of 828 (total number of surveys) instead of 350. I'm confused because the correct number - 350 - is showing for each row in the sub summary report.

      NPS_Score_miscalc.jpg

        • 1. Re: Calculations in SubSummary Report
          philmodjunk

               On what table is this layout based?

               Is each row in your report a sub summary layout part?

               Don't columns 3, 4 and 5 total different calculation fields instead of the same calculation field as you have written here?

               What is the exact expression that you used for column 6?

          • 2. Re: Calculations in SubSummary Report
            sccardais

                 Q: On what table is this layout based?

            A: The Surveys table. "I'm having trouble using Summary fields in a Sub-summary report formatted as a pivot / cross tab table displaying records from a Surveys table using a break field of Product_name."

                 Q: Is each row in your report a sub summary layout part?

                 A: See screenshot below. The layout shown below results in 7 rows with each summarizing the results for each of the company's products / editions.

                 Q: Don't columns 3, 4 and 5 total different calculation fields instead of the same calculation field as you have written here?

            A: You are correct. Cut and paste error above. Columns 3,4,5 based on 3 different calc fields in Surveys table. c_Promoters, c_Passives, c_Detractors.

            Q: What is the exact expression that you used for column 6?

            A: Column 6 is a calculation field from Survey's table. Field name is c_NPSCalc. This is the calculation: (GetSummary(Summary zPromoter;JR Edition)/Count_Surveys)-(GetSummary(Summary zDetractor;JR Edition)/Count_Surveys)

                 CountSurveys field used in the calculation is another field in Surveys table. It is simply a count of all the records with a Survey ID. (all records in database.)

                 CountSurveys is also column 2. It gives the correct # of surveys received for each edition.

                  

            • 3. Re: Calculations in SubSummary Report
              sccardais

                   Correction: CountSurveys is a Summary field in the Surveys table - not calc field.

              • 4. Re: Calculations in SubSummary Report
                sccardais

                     This is a screenshot from Excel of the report I am trying to generate.

                • 5. Re: Calculations in SubSummary Report
                  philmodjunk
                       

                            See screenshot below.

                       But your screen shot does not show a FileMaker Layout. What I need to know is if, in your original screen shot, each row of data shown is from a sub summary layout part. I also need to know the "sorted by" field specified for that sub summary layout part.

                       In your calculation:

                       GetSummary(Summary zPromoter;JR Edition)/Count_Surveys - GetSummary(Summary zDetractor;JR Edition)/Count_Surveys

                       The fields in blue and the "sorted by" fields of your sub summary layout parts should be exactly the same.

                  • 6. Re: Calculations in SubSummary Report
                    sccardais

                         Hope this answer your question.

                         There is one sub summary part on the layout. The break field is JR_Edition - same as fields you highlighted in blue.

                         In an earlier post, I incorrectly described Count_Survey as a calculation field. It is not. It is a Summary field in Surveys.

                         c_NPSCalc is calculation field in Surveys that uses Count_Surveys.

                         Perhaps I should be using the GetSummary function with Count_Surveys in my formula? e.g. 

                    GetSummary(Summary zPromoter;JR Edition)/GetSummary(Count_Surveys;JR Edition) - GetSummary(Summary zDetractor;JR Edition)/GetSummary(Count_Surveys;JR Edition)

                    ??

                    • 7. Re: Calculations in SubSummary Report
                      philmodjunk

                           I had wondered about that detail and that explains it. The Count_Survey field is returning a "grand total" type count in your calculation. Modify your calculation as you have posted and it should return the correct result.

                      • 8. Re: Calculations in SubSummary Report
                        sccardais

                             Success. That did it.

                             Using GetSummary with Count_Surveys in the calculation did it.

                             Thanks very, very much for your help.