AnsweredAssumed Answered

Calculations in SubSummary Report

Question asked by sccardais on Mar 5, 2014
Latest reply on Mar 5, 2014 by 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

Outcomes