Conditional Formatting of Table

[BrianDumm]

So, I have been wracking my brain over this one.  

My users have very specific requirements for the formatting of the output.  

Currently I am doing it in an excel macro workbook, using VBA, and generating an email with the grid in the body of the email.  I have this set up on a desktop machine and auto running at various times through the day.  We have issues when the machine boots, power cycles, software updates etc. 

I am trying to convert it into Alteryx, and run it from the Gallery on a schedule.  

I have managed to reproduce this in an Alteryx Table, merging the Headers in rows 5 and 6 into one Alteryx header.  IE, 017 Days - 08/25/2021.

EXCEL OUTPUT

ALTERYX OUTPUT:

I have achieved this much with the help of the MACRO in this post as a template/base.  The problem is, I have a lot more Categories and Types to add, and they can change, so I don't really want to have to code them all into the statement.

The only things that should be highlighted in the chart, is Any Value > 0, in a Row with Type 1 and in a column that is NOT 1,2, or 3 days, should be RED.

And same condition, but for Type 2, would be YELLOW.

The Columns are dynamic and it will only display for a certain number of days if there is a count in that column.  In other words, if there is nothing 10 days old, I will not have a 10 day column.

Any help or advice would be greatly appreciated!!

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Highlight-cells-that-No-across-whole-w...

'<Rule Name="'+[name]+'Rule1">
<RowRule value="False" />
<HighPrecedence value="False"/>
<Field>'+[name]+'</Field>
<Test>Formula</Test>
<ApplyTo>Data</ApplyTo>
<SimpleVar />
<SimpleOp>==</SimpleOp>
<SimpleConst>1</SimpleConst>
<Formula>

([_CurrentField_] != "0" and [_CurrentField_] != "Category 1")  I think I would need to add ALL of the departments here also, to NOT highlight them, and this is what I want to avoid having to do.  I also can't figure out, how to ID days 1-2 and NOT highlight them.  )
AND
([_CurrentField_] != "0" and [_CurrentField_] != "Category 2")

</Formula>
<Style TextColor="#ff0000" />
<PrettyName>'+[name]+' Rule 1</PrettyName>
</Rule>'

[danilang · Accepted solution]

Hi @BrianDumm 

The macro that you're building on only builds rules for the columns that you pass in.  In your main workflow, after the Field Info tool, use a filter to only select the "xxx days" columns.  That will avoid having to code in the exceptions for the other columns. 

The <Formula> field can also reference any of the other fields in the row as opposed to the just the current one.

Experiment with manually building the formulas in a test Table tool for two of the columns and then look at the generated XML in XML view(see my answer in the original thread that you linked to).  Since you're looking at columns that are not the current one, you'll need to change the formula tool in main workflow to a Multi-row formula so that you reference the field names that are above the current one.

Dan   

[BrianDumm]

This is PERFECT!  I had tried dynamic selecting the columns, and splitting them off so I would only pass the ones I wanted to alter, but I was doing it farther back in the flow, and then trying to Join the results all back together, left table with categories, middle table with all the day columns greater than 3, and then a right table with the columns for 1-3.  It was "working" but did not line up nicely, and had different row heights.

This does EXACTLY what I need it to now.  

Now to fit in the Totals row and columns!

I can't thank you enough.  You have made my week!

[Mond]

@danilang  a few years on, I am also looking into a similar issue for me. If i use the filter option you mention, is there an easy way to re-join columns where the rules do not apply into one table after the macro provides a table output? a bit of a newbie with reporting here... i get the other parts of everything mentioned above.