Latest and Prior year NPS% calculation not showing right numbers

0 votes

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

Jun 9 in Tableau by Rekha
• 160 points

reshown Jun 10 by Cherukuri 42 views
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)

2 answers to this question.

0 votes

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!

answered Jun 10 by Cherukuri
• 21,430 points
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?
0 votes

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
answered Jun 12 by anonymous
• 21,430 points

Related Questions In Tableau

0 votes
1 answer

Not showing a dual axis

On the Rows shelf, right-click on the ...READ MORE

answered Apr 10, 2018 in Tableau by QueenBee
• 1,810 points
30 views
0 votes
1 answer

Is it possible to merge year, month and date in Tableau?

Remove all the date pills and drag ...READ MORE

answered May 9, 2018 in Tableau by Atul
• 5,530 points
30 views
0 votes
1 answer

How to merge Year ,Month and date in Order Date in Tableau?

Hi, you can simply drag the Order ...READ MORE

answered Aug 24, 2018 in Tableau by AwesomeSauce
• 860 points
68 views
0 votes
1 answer

Hover text shows numbers in decimal points

Do this: 1. Right click on the calculated ...READ MORE

answered Apr 2, 2018 in Tableau by QueenBee
• 1,810 points
26 views
0 votes
1 answer

How to calculate profit when cost and revenue are in same column?

You can create the following calculated fields: Profit: SUM(IIF([account_category] ...READ MORE

answered Apr 10, 2018 in Tableau by Atul
• 5,530 points
66 views
0 votes
1 answer

How to dynamically determine and categorize duplicate value in Tableau?

Apparently you define a duplicate records as ...READ MORE

answered Apr 12, 2018 in Tableau by xyz
• 1,550 points
1,643 views
0 votes
1 answer

how to generate random numbers and row-count in Tableau

Hi, You can create random numbers using a ...READ MORE

answered Mar 13 in Tableau by Cherukuri
• 21,430 points
283 views
0 votes
1 answer

LOD expressions- Create a calculation that is not affected by filters.

Hi Nikki, Your LOD expression is correct, but ...READ MORE

answered Apr 2 in Tableau by Cherukuri
• 21,430 points
26 views