1 2 Previous Next 15 Replies Latest reply on May 7, 2013 9:02 AM by Ryan Johnson

# How do i create a calculated field based on text fields?

Sorry - Tableau newbie here, so I apologize in advance!  I need to create a calculated field (I presume) expressed as a percentage, but using text fields.

Specifically, I'm trying to write a won/loss ratio using these table entries: WON, COMPLETED, CANCELLED, LOST.  The ratio = WON or COMPLETED divided by (WON,COMPLETED) + (CANCELLED,LOST).

But I can't seem to figure out how to tell Tableau how to take the number WON or COMPLETED and divide it by the other factors.

• ###### 1. How do i create a calculated field based on text fields?

It depends on whether you have 4 different fields (WON, COMPLETED, CANCELLED, LOST) or if you have a RESULTS field with any of the four as values. Let's assume the later...

Calc 1

IF [Results] = 'Won' OR [Results] = 'Completed' THEN 1 ELSE 0 END

Calc 2

IF [Results] = 'Cancelled' OR [Results] = 'Lost' THEN 1 ELSE 0 END

Calc 3

[Calc 1] / ([Calc 1] + [Calc 2])

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: How do i create a calculated field based on text fields?

Thanks, Shawn!  That seems to do the trick!  When I format the results as percentage though, I get some crazy percentages like 49400% and 9700%.  And nobody has a percentage between 0% and 100%.  Something weird with my data perhaps?

• ###### 3. Re: How do i create a calculated field based on text fields?

Post it back and I'll fix the percent format for you.

--Shawn

• ###### 4. Re: How do i create a calculated field based on text fields?

Ugh - I'm sorry, Shawn. This is my first (of many, I'm sure!) forum post, and I'm not quite sure I follow what you mean.  Post the calculations here?

• ###### 5. Re: How do i create a calculated field based on text fields?

Post the workbook again with your changes that are giving you the wrong percentages. The deal is you may need to use a custom number since your numbers may be coming in as pre-calculated percentages. It easier from me to do it than explain all the possible options.

--Shawn

• ###### 6. Re: How do i create a calculated field based on text fields?

Ryan the whako numbers aren't a formatting problem; they're a calculation problem. Let me investigate further. I'll have something for you in the morning.

--Shawn

• ###### 7. Re: How do i create a calculated field based on text fields?

Thanks, Shawn.  That's what I was thinking as well.  When you look at the two supporting calculations, they seem to work fine - showing # of wins, # of losses.  It's something about what happens when the two are used together in the final #3 calculation...

• ###### 8. Re: How do i create a calculated field based on text fields?

Yep. I was able to use SUM([W/L Wins])/SUM([Number of Records]) to get you into the ballpark. But it's not exact. I suspect it's because there's overlap in all the conditions that make up the two feeder calcs. But I'm too fuzzy to track it down today.

--Shawn

• ###### 9. Re: How do i create a calculated field based on text fields?

Haha!  I bet!  Thanks for working on this.  Have a virtual beer on me.

• ###### 10. Re: How do i create a calculated field based on text fields?

BTW, here's where I am with the workbook. Note that the Losses and Wins don't add up to the number of records. If you see what's going on let me know.

--Shawn

• ###### 11. Re: How do i create a calculated field based on text fields?

It's quite possible there were more quotes than there were quotes described as won or lost.  Some of the quotes had statuses that are irrelevant to what I'm trying to report on.  I went in today and put a data filter on to exclude these "extra" quotes, but that still didn't seem to do the trick.

It's funny that the win/loss ratio number is always the same as the number of quotes won for that client.

Also, I'm not sure if this makes a difference or not but when you click on View Data for one of the records and then look at the underlying data tab, the wins and losses are described as "1" and "0" but the w/l ratio is described as "x.xxxxx" with wins expressed as "1.00000" and losses as "0.00000"....(?)

• ###### 12. Re: How do i create a calculated field based on text fields?

Ryan, I'm sidelined for a while until I hear back from tech support (see post above).

--Shawn

• ###### 13. Re: How do i create a calculated field based on text fields?

Post in this thread?  Sorry to hear you're having trouble.

Anywho now that I've eliminated the extra non-essential records, I went back and tried your SUM([Wins])/SUM([Number of Records]) formula and that seems to work.  Woohoo!  The percentages all work out the way they should.  If I add Losses or Wins as a measure though, it sets the ratios back to 0.  I'm assuming that's a function of the aggregation the ratio is performing or something?  That's not really a requirement for the report thank God, but I was curious...

• ###### 14. Re: How do i create a calculated field based on text fields?

Glad you got it working. On the record level Wins will be 1 or 0 and Losses will be 1 or 0 because that's what we are assigning them in the calculation we are using to convert them from strings to numbers. That's why the underlying data gives you the results you described. This technique only really works if you aggregate the calcs to a level higher than row. Otherwise you're dividing 0 by 1 or 1/1. Neither are very useful.

--Shawn

1 of 1 people found this helpful
1 2 Previous Next