Getting a percentage in Tableau from a yes/no column

1
votes

I have a spreadsheet of support case management data. I am working with this in Tableau. Each line in the spreadsheet is an individual case. Each case has, among much else, a support agent name and a Yes or No of whether the case work was started within 12 hours. I'd like to know, for each agent, what percentage of the time they started the case work within 12 hours. So, if Bob has 2 "No" and 8 "Yes", he should have 8 / (2 + 8) = 80%.

My attempt at this was to create 2 sets. One is the set of "Yes, started within 12 hours" (those that have "yes" in that field, and one that is the set of "No, not started within 12 hours", the complement to the other set. Silly me, I thought I could do something like COUNT(yeses) / COUNT(nos). Nope, big red failure. So what is the right way to do this?

It would help immensely to please respond as if this is the first thing I have ever done in Tableau. It is. I have learned a lot in this project, but only in comparison to the nothing I knew previously. Please also let me know if I've left out something necessary to answer this. I've tried to be complete but, well, am noob...

If it clarifies anything, here's a poor Excel mockup of data and the effect I'm looking for:

enter image description here

2
tableau-api

2 Answers

1
votes

Yes, this is possible and easy in Tableau, but first a couple of points.

The reason your attempt to use COUNT() did not work is that COUNT() does not operate the way you, and that 99% of the people on the planet, expect. COUNT([some expression]) returns the number of records that have a non-null value, any value, for [some expression]. The name comes from SQL relational databases.

The calculations would be just a bit simpler if your third column took the boolean values True or False instead of the string values “Yes” or “No”. (In which case you could drop ‘= “Yes”’ from the formula below)

So two ways to do your calculation are:

  1. Directly with an aggregate calculation which can get the right result, but is hard coded for this case, such as:

SUM(INT([Started within 24 hrs?] = “Yes”)) / SUM([Number of Records])

  1. Using a table calc - which in this case is a bit easier and more flexible. First, Build a table or viz in Tableau showing SUM([Number of Records]) with the dimensions you care about in play. Say with [Name] on Rows, [Started within 24 hrs?] on Columns and SUM([Number of Records] on Text. Second, Right click on your measure SUM([Number of Records]) and choose Percentage of Total from the Quick Table Calc menu. Finally, use that same menu to adjust Compute Using to specify how you want the percentages computed - in this case, using [Started within 24 hrs?]

If you only want to show some of the data, right click on the column header for the values you wish to hide and choose Hide.

The type conversion function INT() converts True to 1 and False to 0.

1
votes

You could create another column that converts the Yes's into 1's, and the No's into 0's. Sum up all the 1's and divide it by the total and that's your percentage.

edit: the new column would look something like

=IF(C3="Yes",1,0)

in other words, if Cⁿ is "Yes", then 1, else 0