Tableau grouping by calculated field

Question

I have a dataset within SQL that pulls sales by client on a product basis. Some clients can have multiple products, while others can have just one. I'm trying create a histogram of clients by their aggregated spend amount, but the classification I'm using is a measure, and I can't figure out how to make that a dimension.

Using the table below, the Sale Amount for AAA would be $7500, BBB would be $4000 and CCC is $14,000. I would use the following :if SaleAmt > 10000 then "10K+", elseif SaleAmt > 5000 then "5K-10K, else "sub-5K" end and then combine it with countd(Client) to get the histogram, ideally it would show 1:2:1 for 10K+, 5K-10K, sub-5K.

But when I try this within Tableau, it treats the if statement result as a measure and not a dimension, not something I can classify around. Is there a better way to do this within Tableau?

Client    Product    SaleAmt
AAA       1          3500
AAA       2          4000
BBB       1          4000
CCC       4          7000
CCC       7          7000 
DDD       5          8000

Actually, the calculated field you posted uses SaleAmt as dimension. The only thing is that it will go line by line, so AAA1 and AAA2 will be sub-5k, and not AAA as 5K-10K. You need to treat your data before plugging it on Tableau, making it one line per client. — Inox

coge.soft coge.soft · Accepted Answer · 2015-03-12T05:59:43

Right-click on Product. Click on Convert to Dimension.

Create a calculated field called Client Tier (or whatever you wish) with the following calculation:

if SUM(SaleAmt) > 10000 then "10K+" 
elseif SUM(SaleAmt) > 5000 then "5K-10K" 
else "sub-5K" 
end

This will appear in your Measures, but dragging its pill to the Rows shelf will show it as discreet and you end up with a desirable result:

Client     ClientTier   SaleAmt
AAA        5K-10K       7,500
BBB        sub-5K       4,000
CCC        10K+         14,000
DDD        5K-10K       8,000

Be aware that adding in Product will "break" your Sales Tiering by Client...

Instead make a calculated field called Client Tier with the following code:

if WINDOW_SUM(SUM(SaleAmt)) > 10000 then "10K+" 
elseif WINDOW_SUM(SUM(SaleAmt)) > 5000 then "5K-10K" 
else "sub-5K" 
end

Once you add the first reference to WINDOW_SUM(), a link will appear in the top right called "Default Table Calculation." Click it and select "Client" under Compute Using. This will give you your Tiers by Client across all products (or any other dimensions you might have).

Here's the result:

Client  Product ClientTier  SaleAmt
AAA     1       sub-5K      3,500
AAA     2       sub-5K      4,000
BBB     1       sub-5K      4,000
CCC     4       5K-10K      7,000
CCC     7       5K-10K      7,000
DDD     5       5K-10K      8,000

Tableau grouping by calculated field

1 Answers