Report Tool - Conditional Format When Value in Cells Repeat

[kheuer]

Hello,

I would like to use the Report tool to format rows when the same value appears in another row. In my specific example, I want to highlight those warehouses that have multiple deliveries coming up. Ideally I want the tool highlight in different colors. Thoughts?

[AbhilashR]

@kheuer - A simple solution would be to

• To identify records with similar deliveries upstream to the table tool
• Using this identifier write a logic in the Table tool to color records.
• Unselect this identifier in the Table tool.

I dont think you can automatically assign different colors to different buckets. You might have to manually assign colors to these buckets.

Attached is a sample solution for your reference. Hope this helps.

[CharlieS · Accepted solution]

Hi @kheuer 

In the attached example, you'll see how I identified duplicate records, then generate a random color to be assign to each unique value. This process randomizes the RGB values, so there is a possibility for duplication, so I included an alternative where you could set a list of RGB values to use. 

Like @AbhilashR suggested, these color assignments need to be determined ahead of time and placed in a field that will not be displayed by the Table output. Here's an example expression to specify a specific background colors to be used in the Formula options of Row Rules in the Table tool:

"background-color:rgb(255,255,255);"

Where the RGB values are integers between 0-255. A list of available formatting expressions can be found here in the documentation:

https://help.alteryx.com/current/TableStyleRules.htm 

With this knowledge, we now know how to prepare RGB values for the Table tool to use.To randomly assign the 0-255 RGB values, I used the following expression: 

"rgb("
+ToString(Round(Rand()*255,1))+","
+ToString(Round(Rand()*255,1))+","
+ToString(Round(Rand()*255,1))+")"

If you want less intense colors (to avoid overwhelming the visual appearance of the table), you could limit the randomized values to only 155-255 with the following alternative:

"rgb("
+ToString(Round(155+Rand()*100,1))+","
+ToString(Round(155+Rand()*100,1))+","
+ToString(Round(155+Rand()*100,1))+")"

Check out the attached workflow for an example of all this in action.

[AbhilashR]

@CharlieS - Learnt something new today - "background-color:"+[Color]+";"

I have been using Alteryx for a few years now, but spending dedicated time and answering question has in-turn taught me so much more than I had imagined. Thank you very much for posting this solution! 

[kheuer]

@CharlieS - woooooooooooooooow! I am sooooooo impressed. 🙂

Thank you soooooo much.

[atcodedog05]

Hi @CharlieS 

Wow!! This is a fantastic hack to dynamically assign colors to groups when there is changing n number of groups.

This was really helpful for me. And an amazing new learning. Thank you @CharlieS  🙂

[daniellefzimmerman]

Thank you for the solution!