Menu

Color the Dupes

cover-image

Not only because it rhymes with my first Tableau post ever, Color the Tips, but also because it's pretty darn cool, here's a post on how to Color Duplicate Records in Tableau.

Even if coloring dupes isn't your top priority, the table calcs / color palette know-how are super useful and they definitely transcend the use case. In fact, stick with me & I'll show you how to assign a distinct color palette to a continuous measure pill. There's juicy insight to be gained by doing so.

My goal was to highlight the strings that appear more than once, each pair with a distinct color. Having trouble with it, I went to the community forums with two problems:

  1. My table calcs worked, but they were kludgy
  2. Since they use aggregations, table calcs are measures
    • and measures can't receive a default color palette that is distinct

Coloring the Dupes (I'm a dupe! because I over-... | Tableau Support Community

Identifying Duplicate Records

My initial approach was over-kill, and I knew it. I was nesting five calcs to identify the dupes & assign them a distinct value for the color. Moreover, I was manipulating strings, which is bad for performance. When computing it's always best to work with numeric values & avoid performing complex logic on strings.

Jonathan Drummy helped me to simplify, and his approach was excellent. He takes the modified competition rank and subtracts from it the standard competition rank. If there's a difference between them, then the record occurs more than once!

 photo color-calc_zpsac43847f.png

Coercing Into a Distinct Color Palette

Coercion is a strong word, but that's exactly what I've done. Jonathan also pointed me to a recent deep-dive into customizing continuous color palettes by Robert Rouse:

Understanding Sequential and Diverging Color Palettes in Tableau

In it, Robert modifies a continuous diverging color palette to insert his own, third color to be used as the "center". And that's what got me thinking! If Robert can squeeze in three colors, then what prevents me from squeezing in four, or five, or twenty?

So that's exactly what I did. I built a custom diverging color palette with twenty different colors in it, each selected as an earthy tone suitable for highlighting text on a white background.

Notice how it's all wavy & continuous.

 photo continuous-20_zps368e132b.png

I've used black in the first position on purpose. This is assigned to all of the singular values that are not a duplicate.

Now things were beginning to click. Just one step further and it was possible to get exactly what I want. By using the full color range, and 20 steps to match the 20 colors in my Preferences.tps:

 photo distinct-20_zpsdb41334f.png

VoilĂ  !

A distinct custom color palette, disguised as continuous, and one that I can apply to a measure pill to accomplish my wish! Big thanks to Jonathan and Robert for their respective contributions & I hope you found this useful!

Check the community forum for detail and attachments.

Word Count: 479

References

  1. "Color The Tips", Keith Helfrich, Red Headed Step Data, May 14, 2014
    http://redheadedstepdata.io/color-the-tips/
  2. "Coloring the Dupes (I'm a dupe! because I over-engineered the solution :)", Keith Helfrich and Jonathan Drummey, Tableau Community Forums, January 7, 2015,
    http://community.tableausoftware.com/message/323549
  3. "Drawing With Numbers", Jonathan Drummey, January 7, 2015
    http://drawingwithnumbers.artisart.org/
  4. "Robert Rouse: Business Intelligence Consultant", InterWorks Blog, January 7, 2015
    http://www.interworks.com/company/our-team/staff/robert-rouse
  5. "Understanding Sequential and Diverging Color Palettes in Tableau", Robert Rouse, InterWorks Blog, December 15, 2014 http://www.interworks.com/blogs/rrouse/2014/12/15/understanding-sequential-and-diverging-color-palettes-tableau
  6. "Creating Custom Color Palettes", Tableau Knowledge Base, August 21, 2014
    http://kb.tableausoftware.com/articles/knowledgebase/creating-custom-color-palettes