6 Replies Latest reply on Nov 4, 2013 12:14 PM by Pei-Zhen Yu

# Calculate AVG of number-looking aliases of a String field

Hello,

As in the attached mock-up, I am trying to calculate the average of 'Answers'. 'Answers' is a String field. And I have created aliases for different ratings"Bad, Okay, Good, Great" as in below screenshot.

Is there a way show the average of the aliases? (In other words, the average of column 'Answers' excluding the text answers)

Pei

• ###### 1. Re: Calculate AVG of number-looking aliases of a String field

The alias cannot be used for creating a calculation like this, as far as I know.  You'd instead want to use a calculation, something similar to:

IF [Answer] = "Good" then 1 //   as an alternative, you could use IF CONTAINS([Answer], "Good") then 1

elseif [Answer] = "Okay" then 2

elseif [Answer] = "Good" then 3

etc, etc.

And an Average on that could be computed.

• ###### 2. Re: Calculate AVG of number-looking aliases of a String field

Thanks Matthew!

This is smart! I made the changes and can see  the new calculated field is ready to compute now. Is there a way to add a row to the bottom of the table just to show the 'Average'? I found myself stuck in the step to make the 'Average' show up..

Thanks!!

Pei

• ###### 3. Re: Calculate AVG of number-looking aliases of a String field

You will want to click on the field you want to average after it has been added to your table.  It should show up in the "Marks" box.  You'll want to change "Measure" from Sum to Average.  Below is an image so you can see what I mean.  The variable you are averaging should look like the below.  To add the row to the bottom of the table with the average, click on Analysis then Totals then click "Show Column Grand Totals."  Hopefully this helps!

• ###### 4. Re: Calculate AVG of number-looking aliases of a String field

Thanks Caitlin！

I changed the Measure to 'AVG', but when I clicked on Analysis then Totals then click "Show Column Grand Totals", it still showed 'Total' instead of 'Avg'. Could you please attach the worksheet that you updated?

By the way, is there a way to keep the default text if 'Answer' is not in (Bad, Okay, Good, or Great). It gave me an error msg when I tried to do below(underlined):

end

• ###### 5. Re: Re: Calculate AVG of number-looking aliases of a String field

I've attached the file with the average in the Grand Total line.

Your equation isn't working because you are pulling a Dimension (String) into a Measure (Number).  Tableau isn't able to have a mixture of String and Numbers for one variable.  You would need to have a Number for the "Else" statement to get it to work.

Hopefully this helps!  Let me know if you have any further questions.

• ###### 6. Re: Re: Calculate AVG of number-looking aliases of a String field

Thank you very much Caitlin! This is really helpful!