Latest and Prior year NPS calculation not showing right numbers

Question

Could you please help with  NPS% by each area calculation.

 Promoter %  Calculation as : sum([Promoter])/[Total no of results]*100

NPS%=[Promoter%]- [Detractor%]

latest Year calculation as :  {max(YEAR([Date]))}

Prior year calculation as : {max(YEAR([Date]))}-1

latest year NPS% calculation as:    AVG(If year([Date]) =[latest year] then {[NPS%]} else 0 end) 

Prior year NPS% calculation  as :    AVG(If year([Date]) =[Prior Year] then {[NPS%]} else 0 end)

I am getting NPS% as % total of all areas (table down cal). I am not getting correct NPS% by each individual Areas .I tried different table calculations and LOD function but none of them worked.

Requirements is to have data visualize in below table format only.

    

Areas	no. of respondents	% of respondents	2018	2019	Var	Q3/18	Q4/18	Q1/19
Practice	5000	10	84.2	80	4.2
Inp	5000	10	80.8			83.9	50.0
Total	50000	100	AVG	AVG

Here is the format of data of one question where I have summarize responses as number of results(it's dummy data)   

Area	NPS Category	no of results	date
Practice	Promoter	50	1/1/2018
Emr	Promoter	150	1/2/2018
Testing	Promoter	4000	1/3/2018
Inp	Promoter	300	3/1/2018
OPS	Promoter	2000	3/2/2018
OpR	Promoter	50	5/3/2018
Practice	Promoter	20	4/4/2018
Emr	Detractor	480	6/5/2018
Testing	Detractor	100	6/6/2019
Inp	Detractor	13	7/7/2019
OPS	Detractor	1000	8/8/2019
OpR	Detractor	25	9/9/2019
Practice	Detractor	45	8/10/2019
Emr	Passive	700	2/11/2019

Comments

What does this line mean? Promoter %  Calculation as : sum([Promoter])/[Total no of results]

Is it the sum of records with promotor / total no of records of all NPS Category

or sum of no of records of promotor by total no of records for all NPS category

---

it is sum of no of records of promoter/ total no of all NPS Category( Promoter+ Passive+ Detractor)

Answer 1

Hi Rekha,

Create a calculated field as below to calculate promotor and detractor,

IF [NPS Category] = "Promoter" THEN [no of results] ELSE 0 END 

IF [NPS Category] = "Detractor" THEN [No Of Results] ELSE 0 END

Now calculate the NPS% as SUM(ZN([promotor]) - ZN([detractor])) / SUM([No Of Results])*100.

To create a table to show area wise and year wise NPS%, follow the below steps:

1. Drop the Area field in row shelf and date in column shelf and convert it to YEAR(Date) in discrete format.

2. Now drop the NPS% calculated field in text shelf and convert the aggregation to AVG.

3. The table will show like below assuming on dummy data as per your question.

Hope it helps!

Comments

Cherukuri, I was actually asking how to show combined total no of results for 2018 and 2019 in one column( total no of results for all years) and NPS% for 2018 and 2019 in separate column( individual year columns).

Also very last column showing difference in NPS% from prior year. Thanks!

---

oh! did you find the solution to the problem?

Answer 2

Try using code like below,

For NPS% use code as 

SUM(ZN([promotor%])) - SUM(ZN([detractor%]))

For promotor% / detractor% as 

IF [NPS Category]="Detractor" THEN [No Of Results]/{SUM([No Of Results])}*100 END

Comments

Hey! Register at Edureka Community and contribute(ask, answer or comment) and earn points and reputations.