6 Replies Latest reply on Sep 8, 2013 1:38 AM by Steve Martin

# Calculated Field based on dimension values

Hello,

I'm trying to modify the calculated field for Profit Ratio in the attached workbook.  I want to modify it so that it only displays the Profit Ratio if the Category = Furniture, and the Sub-Category <> Tables.  For all other values, i want the profit ratio to show null.

I want the Sub-Total for Profit Ratio for Furniture to be displayed, and the Grand Total as well (these two values should be the same).

Thanks!
Jackie

• ###### 1. Re: Calculated Field based on dimension values

Hi Jackie,

When you say display, do you mean in the tooltip or actually on the chart?

Steve

• ###### 2. Re: Calculated Field based on dimension values

Hi Jackie,

I think this is what you meant. Apologies but this has now been saved as a Tableau 8 Workbook, I can just provide the calc if you cannot open this book.

Steve

• ###### 3. Re: Calculated Field based on dimension values

This is simpler:

If ATTR( [Category] ) = 'Furniture' AND ATTR( [Sub-Category] ) = 'Tables'

Then 0

Else [Profit Ratio]

End

And would run faster against a large database.

• ###### 4. Re: Calculated Field based on dimension values

Thanks... is there a way to show the value as Null instead of 0 but still have it compute the Subtotals and Grand Totals?

• ###### 5. Re: Calculated Field based on dimension values

Sure,

If ATTR( [Category] ) = 'Furniture' AND ATTR( [Sub-Category] ) = 'Tables'

Then NULL

Else [Profit Ratio]

End

--Shawn

• ###### 6. Re: Calculated Field based on dimension values

Shawn's method is correct in terms of speed though there is more than one way to skin a cat and this could also be written as an inline-if which is also great for users coming from Excel and not used to a full if or case statement:

Iif(Attr([Category] = 'Furniture' And Attr([Sub-Category]) = 'Tables',Null,[Profit Ratio])

As I said, Excel users used to nesting functions can easily work with this however I still stand by the method I proposed earlier as imo that method provides the greatest flexibility (and readability) in allowing you to perform multiple actions for different events.

It really is now down to whichever method you feel most comfortable with and produces the best results for your viz.

Also, when nulling, it would be advisable to tell Tableau how to handle the nulls (the default is to show at indicator) though this can easily be changed by selecting Format (on the calc pill) > Pane and adjusting the Special Values.