How to calculate NPS on Spotfire

[Ricardo Araki]

It seemed to be very simple at beggining. I have a file with a column, named "Response Type"where we classify the customers as "Promoter", "Neutral" and "Detractor".

NPS score isa simple calculation as:(%Promoters) - (% of Detractors).

Because this column also have other values as "positive" and "negative", I added a calculated column, "NPS_Count"just to make it easier to count, so every time theres a "Promoter", "Neutral" and "Detractor", this calculated column will have "1".

Then I created another calculated column, "Total_det_prom_neut",to have the total of "Promoter", "Neutral" and "Detractor" so I could calculate the percentage of "Promoter" and "Detractor". Its like this:

 

My thought was to first have the total of responses, so for Total_det_prom_neut I used:

 

case

when [Response Type] in ('Promoters', 'Neutrals', 'Detractors') THEN Count([NPS_count])

ENDThen I tried to calculate the percentage of "Promoters" and "Detractors":

 

CASE WHEN [Response Type]='Detractors' THEN

Count([NPS_count]) OVER ([Response Type]) / [Total_det_prom_neut]

WHEN [Response Type]='Promoters' THEN

Count([NPS_count]) OVER ([Response Type]) / [Total_det_prom_neut]

ENDIt seems to be correct so, finally, Im trying to calculate the NPS and here is where everythings goes wrong. The result is always wrong, or most of times it does not even have a result, as you can see on the print at beggining of this post. My last attempt was this:

case when (

case when [Response Type]='Detractors' then [Promoters_detractors_Percentage] END)

[Fabian Duerr]

You can use the following fromular:

First(If([Response]="Detractor",Count() OVER ([Response]) / Count())) - First(If([Response]="Promoter",Count() OVER ([Response]) / Count()))It has multiple parts...

Count() OVER ([Response]) / Count() will give you the percentage for each value in [Response].

If([Response]=... will give you the results only where this statement is TRUE

And theFirst(If(...) statement will then take this result for all the rows.

So if you break it down into multiple columns it would look like this (only Detractor as an example here):