Tableau: creating calculated field based on cell values

2
votes

Say I have the following worksheet table (the measure is an aggregate sum):

    Code    A   B
     123        10
     456    20  30
     789    40

I'd like to create a calculated field (dimension) that I could use to filter out both the first and third row, in other words, keep only that record where A and B are both positive for the given Code. I've tried IF/ELSE with sum(measure) > 0 in the conditions but apparently I can't mix measures and aggregates. Thanks for any pointers.

1
tableau-api
It's been a while since I've gone hands on with Tableau but, if I rememeber correctly, you can create a dimension by using scripted SQL. If that is still the case you can write something like SELECT [Code],[A],[B] from dbo.[SomeTableHere] where A>0 AND B>0. I'm not sure what information you want to pull into Tableau, so I've added all 3 columns to the SELECT. Sounds like you just want [Code], though. – Kris Gruttemeyer

1 Answers

2
votes

One solution is to create a dynamically computed set based on the Code dimension that only includes codes that meet your criteria. You can do that by:

  • Selecting the Code dimension in the data pane
  • Create a set based on the Code dimension
  • Choosing "Use All" on the General tab of the Set definition dialog
  • Selecting "By Formula" on the Condition tab and entering the formula Sum(A) > 0 and Sum(B) > 0

Once you have defined your set, you can place it on the filter shelf to reduce the data used in the visualization, or use it on other shelfs to display codes in the set, or combine it with other sets to form new ones. You can also refer to sets by name in calculated fields to test membership.

The set above has the same effect as the following SQL:

select Code, ... from ... group by Code having sum(A) > 0 and sum(B) > 0