7 Replies Latest reply on Jan 7, 2013 9:25 AM by Jonathan Drummey

# 911 Calculated field help COUNTIF

I need to create a calculated field that mimic excel's COUNTIF.

I have customer service servey reports with a scale of 1-10.  I need to have separate calculations for

9-10 - called NPS Promoters in the workbook

7-8 and Called NPS Passive

0-6 Called NPS detractors

I would like to get a count of each group of numbers.

My COUNT ([Q1]>9) gives me back the same count as COUNT([Q1])

I hope someone is working on Sunday!

• ###### 1. Re: 911 Calculated field help COUNTIF

Hi John,

Instead of counting occurrences, try converting each occurrence to a '1' or '0' value and summing them. For example, the count of Q1 > 9 in your data set is 12,157 using this formula:  SUM(IIF([Q1]>9, 1, 0))

Does this help?

-Robert

2 of 2 people found this helpful
• ###### 2. Re: 911 Calculated field help COUNTIF

Thanks for helping out, especially on a Sunday.

Real close, I need to be able to generate % based on the counts with your example I came up with some working formulas.

Promoters

COUNT(IF [Q1]=9 OR [Q1]=10 THEN [Q1] ELSE NULL END)
Passive
COUNT(IF [Q1]=7 OR [Q1]=8 THEN [Q1] ELSE NULL END)
Detractor
COUNT(IF [Q1]<7 THEN [Q1] ELSE NULL END)

But now when I use the % formulas, the caluclated fields reset to the total instead of the counted subset.

I added the pcnt formula to the measure values and that's when it goes south, I dont get an error in the formula dialog box.

I can fix it by an undo and an refresh of the data.

• ###### 3. Re: 911 Calculated field help COUNTIF

Hi John,

What '%' formulas are you referring to? You'll need to be a lot more specific for me to understand what you're after. Preferably post a .twbx of your workbook with the formulas you wrote so this is less of a mystery.

-Robert

• ###### 4. Re: 911 Calculated field help COUNTIF

I was using a "%" sign as part of a field name and for some reason tableau didnt like it.  I changed it and what do you know? it worked! The twbx file is attached.

How are you with conditional formmating? I would like to format the NPS REP SAT and NPS LTR fields with green for 65% and above, 65-60 yellow and 60 and below red.

I have used the following calculated field on a diffent viz, and it works fine

If [Ease of Doing Business]< .8 then "red"
elseif [Ease of Doing Business] <= .85 then "yellow"
else "green"
end

but cannot figure out how to two color calcualted fields on the same table.

• ###### 5. Re: 911 Calculated field help COUNTIF

The formula highlights the entire row, rather than the column.  I need to end up with the NPS REP SAT column with conditional highlighs as well as the NPS LTR highlight.

The color formatted is attached

• ###### 6. Re: 911 Calculated field help COUNTIF

I will still need some assistance on the fomatting the columns, but the final dashboard came out pretty good.

• ###### 7. Re: 911 Calculated field help COUNTIF

Hi,

Since you're using Measure Names/Measure Values to build the table, there is only one Marks Card with only one Color Shelf, and unfortunately Tableau does not give us access via calculated fields to the particular Measures in a particular column. That'll change somewhat in Tableau v8, with limits. In any case, the workaround is to use a multiple axis crosstab, I outlined the technique in http://public.tableausoftware.com/views/conditionalformattingv4/Introduction.