Latest and Prior year NPS calculation not showing right numbers

+1 vote

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, 2019 in Tableau by Rekha
• 170 points

reshown Jun 10, 2019 by Cherukuri 1,083 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.

+1 vote

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, 2019 by Cherukuri
• 33,030 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?
+1 vote

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, 2019 by anonymous
• 33,030 points
Hey! Register at Edureka Community and contribute(ask, answer or comment) and earn points and reputations.

Related Questions In Tableau

0 votes
0 answers

Tableau not showing all data in box and whisker plot

In tableau, I'm trying to make a ...READ MORE

Apr 13, 2022 in Tableau by Neha
• 9,060 points
432 views
0 votes
1 answer

Not showing a dual axis

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

answered Apr 11, 2018 in Tableau by QueenBee
• 1,820 points
2,313 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 10, 2018 in Tableau by ffdfd
• 5,550 points
1,301 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
2,344 views
0 votes
1 answer

Showing absolute and percentage values in Tableau

I recreated a data like yours, Follow these ...READ MORE

answered Apr 5, 2022 in Tableau by Neha
• 9,060 points
3,711 views
0 votes
1 answer

Hover text shows numbers in decimal points

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

answered Apr 3, 2018 in Tableau by QueenBee
• 1,820 points
624 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 ffdfd
• 5,550 points
901 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, 2019 in Tableau by Cherukuri
• 33,030 points
17,544 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, 2019 in Tableau by Cherukuri
• 33,030 points
2,169 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP