Need Help with Calculating Weighted Average for Pre-Averaged NPS Data

Question & Answer
, ,
1

I’m working on calculating a weighted average of Net Promoter Score (NPS) based on the number of survey responses. The challenge is that the NPS data I have is already averaged (imported from SmartSheets). When attempting to calculate the weighted average, I get an error related to nesting aggregate functions. The error message is:

Nesting of aggregate functions like / and SUM(AVG(AVG("NPS") * "Survey Responses")) / NULLIF(SUM("Survey Responses"), 0) is not allowed.

I’ve also tried creating two separate calculated fields:

  1. For NPS multiplied by the Number of Responses
  2. For just the Number of Responses

However, I still run into an error about mixing aggregate and non-aggregate functions.

Does anyone have a workaround for this issue?

Thank you in advance for any help!

2

Hello @QS-chev, I believe the main issue you are running into is trying to utilize the NullIf function inside of the division statement. You might have more success if you create a calculated field with an ifelse statement that you can then utilize in your denominator rather than the Survey Responses field.

ifelse({Survey Responses} > 0, {Survey Responses},0)

Let me know if that helps!

3

Thank you for the response and suggestion Dylan. That fixed the null issue. Thank you so much!