Tableau - Use different palette according to name

Question

What I am looking for is to assign a color palette to a subcategory. I haven't find any information online for my problem. I'll explain using dummy values:

Imagine I have school data, with teachers, students and janitors names. On the database, their names are preceded by their job at school (eg: prof-John, st-Trinity, func-Manuel). The purpose is to build a graph with the years on the job of each person. It is possible to create a calculated field and assign blue to teachers, red to students and green to janitors using the contains function. However, I want to distinguish (in the graph) each person within its job, assigning instead of the color blue for teachers, a blue palette for teachers and follow the same ideia for students and janitors.

Does anyone know how to do it? Thanks in advance

Gangue

Answer 1

The easiest approach is to make sure you have two discrete (i.e. blue) fields, usually dimensions. Say one is profession and one is years on the job bin. You can use the create bins command to create a discrete bin dimension based on the years on the job measure. You can adjust the bin size by editing the bin field.

Then you can place two discrete fields on the color shelf if you hold down the SHIFT key when adding the second field. In that case, Tableau will effectively create a combined field and assign colors intelligently. You can edit the color assignments by double clicking on the color legend.

Here is an example.

Confidant

Thanks for the info. This really was what I was looking for. I wonder if you can help me with other issue...Tableau actually assigns the colors intelligently like I needed. But suppose I wanted Corporate in blue and consumer in orange. By double-clicking you can only change the combination of the two fields. And if I remove the profit to change Customer segment colors, when I put Profit again it goes back to the previous ones. I could change the colors one by one but if another combination is added on the database I wonder if Tableau assigns a default color and not the one I want – Gangue

If you do this frequently. You can select two fields in the data-pane (left margin) that you work like this frequently. Right click to create a combined field, choose the delimiter you like for presentation. Then right click again and set the default color palette by hand. Then whenever you use that combined field on the color shelf, you would use those colors. A little hassle to set up the first time, but you could change the default colors later and they would apply to all worksheets. – Confidant

Answer 2

EDIT: This solution gives you a color palette for continuous data. If you're looking to assign colors to discrete fields, this is clearly overkill. Alex Blakemore's suggestion to discretize your continuous data simplifies this process a LOT. But if you're feeling frisky and want a continuous color palette for each member of your dimension, this oughta do the trick.

---

Tableau doesn't let you assign entire palettes to members of a dimension, but I came up with a solution for you. A few caveats:

1. Tableauing always seems to be one silly hack after another, but this is truly the most hacktastic thing I have ever done in Tableau.
2. You're about to do a lot of manual work. Nothing about this process is even remotely dynamic.
3. This solution is extremely fragile. If your data currently contains nothing but students, professors, and janitors, but one day, you add a person that's an adventurer, this will break immediately, and you'll have to go redo a lot of your work.

So... consider yourself warned.

---

For simplicity, I'm just going to have two Roles in the data, but the formulas I use will be generalized for any number of Roles. I also added Age, just so we have a measure to work with.

+-------------+-----+
| Person      | Age |
+-------------+-----+
| prof-John   | 53  |
| st-Trinity  | 22  |
| prof-Andrew | 47  |
| st-Alice    | 21  |
| st-George   | 20  |
| st-Frank    | 21  |
| prof-Ed     | 74  |
| st-Ralph    | 26  |
| st-Skrillex | 18  |
+-------------+-----+

---

Let's start with the easy part. Tableau has a neat option called split that splits fields on delimiters. If you do a custom split, you can choose the delimiter, but Tableau is pretty clever, so if you just choose Split, there's a solid chance it will figure things out for you.

So right click on [Person] and click on Transform/Split. It will give you two calculated fields, the first of which looks like this:

TRIM( SPLIT( [Person], "-", 1 ) )

It should be pretty clear what that's doing, and equally clear what the second field will look like. Let's go ahead and rename those fields to Role and Name, so our table looks like this:

+-------------+------+----------+-----+
| Person      | Role | Name     | Age |
+-------------+------+----------+-----+
| prof-John   | prof | John     | 53  |
| st-Trinity  | st   | Trinity  | 22  |
| prof-Andrew | prof | Andrew   | 47  |
| st-Alice    | st   | Alice    | 21  |
| st-George   | st   | George   | 20  |
| st-Frank    | st   | Frank    | 21  |
| prof-Ed     | prof | Ed       | 74  |
| st-Ralph    | st   | Ralph    | 26  |
| st-Skrillex | st   | Skrillex | 18  |
+-------------+------+----------+-----+

---

It's eventually going to be important that you add a serial ID for each of the Roles, starting at 0. We'll be using that number for some math later. Since we only have two Roles, we can just do it manually without too much effort:

1. Role #

   IF [Role] = 'st'
   THEN 0
   ELSEIF [Role] = 'prof'
   THEN 1
   END
   

If you have more values than that, then you'll need to come up with something clever, but frankly, if you have enough values that doing this manually would be a challenge, then you probably shouldn't be giving each of those values its own color palette anyway.

---

Now the hard, hideous, hacktastic monstrosity of a solution I've concocted for you. We're going to make a custom color palette. (You're going to need to understand how that works for the rest of this post to make sense, so click on that link if you don't know how to make custom color palettes. No worries, it's easy.) More specifically, we're going to build a single sequential palette with a region for each of your Roles.

Our goal will be to normalize and manipulate our data so that the students are in the green region and the professors are in the blue region. Let's start with the normalization.

We're going to need the minimum and maximum ages in each Role, so we'll use LOD expressions:

2. Maximum Age:

   { FIXED [Role] : MAX([Age]) }
   

3. Minimum Age

   { FIXED [Role] : MIN([Age]) }
   

Now let's normalize the ages:

4. Normalized Age Value (NAV)

   ( ([Age] - [Minimum Age]) / ([Maximum Age] - [Minimum Age]) )
   

We now have a [Normalized Age Value] (henceforth NAV) between 0 and 1 for each person, normalized within each Role. Our data now looks like this:

+-------------+------+--------+----------+-----+-----+-----+------+
| Person      | Role | Role # | Name     | Age | Min | Max | NAV  |
+-------------+------+--------+----------+-----+-----+-----+------+
| prof-John   | prof | 1      | John     | 53  | 47  | 74  | .22  |
| st-Trinity  | st   | 0      | Trinity  | 22  | 18  | 26  | .5   |
| prof-Andrew | prof | 1      | Andrew   | 47  | 47  | 74  | 0    |
| st-Alice    | st   | 0      | Alice    | 21  | 18  | 26  | .375 |
| st-George   | st   | 0      | George   | 20  | 18  | 26  | .25  |
| st-Frank    | st   | 0      | Frank    | 21  | 18  | 26  | .375 |
| prof-Ed     | prof | 1      | Ed       | 74  | 47  | 74  | 1    |
| st-Ralph    | st   | 0      | Ralph    | 26  | 18  | 26  | 1    |
| st-Skrillex | st   | 0      | Skrillex | 18  | 18  | 26  | 0    |
+-------------+------+--------+----------+-----+-----+-----+------+

---

Now we need to move the professors to the blue region of our palette, and this is where things get a little tricky.

In a perfect world that exists only in our dreams, we could just add 1 to our professors' NAVs, giving us NAVs between 0 and 1 for our students and between 1 and 2 for our professors, but what our dreams didn't notice is that because we're using a sequential color palette, there are "dead zones" between each region of the palette.

Let's say we've built a sequential color palette with just two colors for each color region. Let's call them Green0, Green1, Blue0, and Blue1. There will be a space between Green1 and Blue0 where the color is continuously shifting from green to blue, thus making this area unusable in our palette. We can drive this point home by discretizing the palette:

That greenish/bluish section in the middle is actually lighter than Green1. So we need to make sure that our students only get the area of the palette between Green0 and Green1, and that our professors only get the area of the palette between Blue0 and Blue1.

We should not consider Green0 and Green1 regions on our palette — they are points. And these points have split up our palette into three distinct regions, the Green Zone, the Dead Zone, and the Blue Zone.

Since the Dead Zone is just the space between our two color regions (between our final green color and our first blue color), we can lower the size of the Dead Zone by adding more colors, which I don't think is necessarily valuable, but it is worth noting. Here's what the zones look like if we build a palette with ten colors per region.

Now if we add more color regions to our palette (for example, a Red Zone), that will add more Dead Zones.

Now we just need the starting point of each color zone and the size of a color zone, and we wind up with the formula:

[NAV] * [Color Zone Size] + [Color Zone Starting Point]

It's not hard to math out the size of the zones when you know the size of the Dead Zones:

So the formula for the size of a color zone is:

5. Color Zone Size

   ( ([numColorCodes] / [numColorZones]) - 1 ) / ([numColorCodes] - 1)
   

The start point is easy to derive from there — it's just the size of a color zone plus the size of the subsequent dead zone. We'll need to multiply by that Role # we calculated earlier:

6. Color Zone Start Point

   ( ([numColorCodes] / [numColorZones]) * [Role #] ) / ([numColorCodes] - 1)
   

So, to reiterate, our color field will be:

7. Color Coordinate

   [NAV] * [Color Zone Size] + [Color Zone Starting Point]
   

I went ahead and put this together — here's a bar chart as a proof of concept.

And, as a bonus, here's the 20 code color palette I made.

<color-palette name="Hacktastic" type="ordered-sequential">
  <color>#DBE9B1</color>
  <color>#BFE38D</color>
  <color>#A7DA72</color>
  <color>#92D064</color>
  <color>#80C45D</color>
  <color>#70B557</color>
  <color>#62A74D</color>
  <color>#569A33</color>
  <color>#498E0F</color>
  <color>#398300</color>
  <color>#B3D4DB</color>
  <color>#8CCCE0</color>
  <color>#71BFDF</color>
  <color>#63ADD6</color>
  <color>#4592C2</color>
  <color>#2B7FB7</color>
  <color>#1471B3</color>
  <color>#1660A2</color>
  <color>#1C508C</color>
  <color>#24446F</color>
</color-palette>

Now obviously, you'll need to add another color to that palette to include the janitors. Unfortunately, that bit is always going to be manual, but you can save a few color palettes with different numbers of zones that you can recycle in the future. You can also have Tableau count the number of color zones you'll need with:

{ FIXED : COUNTD([Role]) }

Godspeed.

Hermie

Ha, agreed, but honestly, it's not as bad as it looks! I was just very thorough, to ensure that it was clear why I was doing the things I was doing. Your solution to simply discretize the data is great! Unfortunately, though, if you want to keep the data continuous, I can't think of a better solution than my admittedly somewhat convoluted custom color palette idea. It'd be neat if there was a more direct route, though! (Anyone?) – Hermie

That said... Yeah, I'd probably just discretize it... Great solution! – Hermie