You might not believe that the Net Promoter Score is the “single most reliable indicator of a company’s ability to grow” but you are likely to be asked to provide an NPS or another score that is an alternative to a simple mean. For this article, we’ll talk about how to create a NPS using derived elements. Derived elements can be used to provide any score that is based on assigning values to categories.
Let’s use this as our net promoter question:
Promoters give the company a score of 9 or 10. Detractors give the company a score of 0 to 6. Respondents who give a score of 7 or 8 are not counted. The response categories are defined as _0 to _10 plus a hidden NA category to add a bit of complication. The NPS for this question would be calculated as follows:
(_10 + _9 - _6 - _5 - _4 - _3 - _2 - _1 - _0) * 100 /(Base - NA)
The number of respondents who skipped the question must be subtracted from the base in order to get the correct denominator.
In order to avoid divide by 0 errors, we need to make sure that the denominator is not 0 by using an IIf function so the calculation becomes:
(_10 + _9 - _6 - _5 - _4 - _3 - _2 - _1 - _0) * 100 / IIf(Base - NA, Base - NA, 1))
Now that we have the calculation, we can set it up in Reporter.
- Drop the net promoter question on to a table.
- Double click the net promoter question to edit it.
- Our calculation uses the Base which requires the base category to have a usable Name. The default name of Base will cause a syntax error in our expression because base is a keyword. The user interface does not allow us to change the name of the Base category so as a first step add some factors which will be useful if you also want to display a mean.
- In the screenshot below, the Script and Properties panes are displayed because we here at smarter Dimensions are hopeless geeks and because the only place that we can change the name of the Base is in the Script pane at the bottom of the dialog.
- Add a name for the Base category and choose Apply.
- Now that we have a name for the Base category, we can insert our net promoter score category.
- Add a derived category.
- Edit the expression using the Edit Item… button. You can copy and paste into the edit box or drag and drop the categories into your expression.
- While still in the Edit Categories dialog, add a Mean too.
- Now that we have the expression, we can test the table so close all the dialogs and populate the table.
- The percentage doesn’t make sense on the Net Promoter Score so we need to set it up to show counts only. This requires the geeky syntax are area of the Edit Variable dialog again.
- The final table looks like this.