6 Replies Latest reply on Apr 1, 2015 4:29 PM by Shawn Wallwork

# Crated a calculated field base on two conditions

Hello,

I am new to Tableau, but I have used other BI tools before.

I would like to create a field that is base of two conditions from other fields, for instace:

 Order Product USD Deal Bucket Calculated Deal Bucket 1 A 100 Less than 1K Less than 500 1 B 100 Less than 1K Less than 500 1 C 100 Less than 1K Less than 500 2 A 200 Less than 1K Greater than 500-Less than 1K 2 B 200 Less than 1K Greater than 500-Less than 1K 2 C 200 Less than 1K Greater than 500-Less than 1K 3 A 500 Greater than 1K Greater than 1K 3 B 500 Greater than 1K Greater than 1K 3 C 500 Greater than 1K Greater than 1K

I need to further split the orders that are Less than 1K.

IIF [Deal Bucket] = "Less than 1K" AND the sum of USD by Order < \$500, "Less than 500",

IIF [Deal Bucket] = "Less than 1K" AND the sum of USD by Order > \$500, "Greater than 500-Less than 1K", [Deal Bucket]))

I saw a couple of post wher somebody was asking for an equivalent in Tableau of the AGGR() function in QlikView, if something like that exists, would be aweome.

So far I have is a normal table created in Tableau, and I am not showing the sum of USD at a detail level, meaning I am showing by Country for instance and then I would need to further brake it down by the needed Deal Bucket.

Thanks for any help.

• ###### 1. Re: Crated a calculated field base on two conditions

Like this?

N.

• ###### 2. Re: Crated a calculated field base on two conditions

Hi Noah,

Your approach is interesting, I am new to Tableau so I think your suggestion may be useful for other things I need to created but for this particular thing I am trying to accomplish now it won't work.

Your solution works as long as the order ID is in the columns, I need something that will work even if the Orders and Products or not showing up, for instance withe data sample I provided I would like the following output:

 Calculated Deal Bucket Sum of USD Less than 500 300 Greater than 500-Less than 1K 600 Greater than 1K 1500 Grand Total 2400
• ###### 3. Re: Crated a calculated field base on two conditions

I see. You actually want these as dimensions? Since these calculations require aggregation to the order level I don't think you can create dimensions based on aggregates in the current version of Tableau. The upcoming release may address this, however, with the help of level of detail calculations. There are many workarounds if you're just looking to create an expanding hierarchy or the view above. I'm not sure I totally understand your goal, but some topics to look at are data blending, table calculations and custom sql as all of these would allow you to layer aggregated data onto the main datasource. You could also create these values on your database or in a view, but that isn't exactly a Tableau solution.

N.

• ###### 4. Re: Crated a calculated field base on two conditions

This is silly that such a tool doesn't have this functionality.

I even talked with a more experience developer and he suggested other workarounds but one way or another Table did not do this.

I had to basically do this in the custom SQL view, and perform a series of select statements and left joins.

• ###### 5. Re: Crated a calculated field base on two conditions

+1 Level of Detail calculations will handle this nicely in version 9. Once it releases, you can throw your SQL out

• ###### 6. Re: Crated a calculated field base on two conditions

Yep! And just in case anyone is interested:

1. Our Beta licences expire in 13 days (4/12/2015)
2. And we've been told there are no other beta releases coming

Read into that what you will.

Cheers,

--Shawn