5 Replies Latest reply on Apr 1, 2016 6:02 AM by Ivan Biger

How make set based on calculated field

Hi to everyone!

I used Tableau for several years, but it's first time when I need help.

I have dataset which is based on phone call surveys. It has several main dimensions: [Company], place-dimensions: [Okrug], [Region], [City]; [Date] and [NPS_KTV_type]. [NPS_KTV_type] describes the type of responder and contains 4 different values: Critic, Neutral, Promoter and No answers. The crucial measure of the whole work is an NPS-index. NPS-index is a difference between the proportion of Promoters and Critics. It should calculated on different levels across different dimensions and filters.

First of all, I made calculated field

[percent of total]

COUNT([NPS_KTV_type]) / TOTAL(COUNT([NPS_KTV_type]))

Than I used in another calculated field

[NPS-KTV-real]

(ZN([percent_of_total]) - LOOKUP(ZN([percent_of_total]), FIRST()))*100

Unfortunately, I couldn't made the difference from "Critic" value, only from FIRST(), and sometimes it created wrong calculations. Also, I need to have some threshold to calculate NPS based on counts of values Critic and Promoter. Any of them should have at least 2 value, unless it has no statistical sense in NPS. I have no idea, how to do this.

So, to use  [NPS-KTV-real] in a way I need, I just hide another values (Critic, No answers, Neutral). I know it's rather bad pratice to do like that, but I don't know how to create a set of only Promoter calculations, which is actually is NPS-index.

I have many troubles when I tried to calculate average on different levels for NPS-index. I tried WINDOW_AVG with no success. And then I realized that I need to use something instead of this secondary calculations trick.

I would appreciate any help.

• 1. Re: How make set based on calculated field

Hi Ivan,

Can you put it ina simpler way what you want to acheive just tell us your expected , we have your workbook.

• 2. Re: How make set based on calculated field

What I try to achieve:

1. Calculate difference for [NPS_KTV_real] only from Critic, not from FIRST()

2. Create condition to filter [NPS_KTV_real] only to those, which calculated when Critic > 1 AND Promoter > 1.

3. MAIN THING. To build the same charts as now in dashboards, but with reference line (bullet chart), which shows average of [NPS_KTV_real] of all companies on the level (okrug, region, city) independenly of filtration.

• 3. Re: How make set based on calculated field

OK Lets take first one

for NPS_KTV_real : Can you please take a example and  tell me output?

• 4. Re: How make set based on calculated field

I think all three points is linked, and may be the last will lead to change the whole approach.

So, first one.

In column [NPS_KTV_type ] you can see values Critic, Promoter and so on. So, Mostly [NPS_KTV_real] is calculated right in actual workbook, but sometimes it returns 0, when there is no data for some regions. [NPS_KTV_real] is normally is difference between proportion of Promoter and proportion of Critic. In my calculated field it differs any value from the FIRST. Mostly, it is Critic and all is right, but sometimes it is Null, and all calculations are wrong.

So, in table calc I could define to differ only with Critic, but in calculated field I don't know how to do that.

If you need some more explanation, I will give some simple screenshots.

• 5. Re: How make set based on calculated field

So, I solve my first two issues. But I would really appreciate help on third issue:

To build the same charts as now in dashboards, but with reference line (bullet chart), which shows average of [NPS_KTV_real] across all companies on the level (okrug, region, city) independenly of filtration.