8 Replies Latest reply on Jan 29, 2018 7:34 AM by Joe Oppelt

# Conditional color formatting average payment term spend based on terms and dollar amount

I am trying to color format the highest three commodity spend with payment terms of N45 or below red (negative) and the highest two commodity spend with payment terms of N75 or higher green (good).  I am guessing this has to be done with some type of if and rank formula but I can't get the syntax to work.  I have named the calculated field "Color Format".

• ###### 1. Re: Conditional color formatting average payment term spend based on terms and dollar amount

Yes, you will have RANKs in there.

I can't open a .TWB file.

Do FILE -> "Export Packaged Workbook" to make a .twbX file.

• ###### 2. Re: Conditional color formatting average payment term spend based on terms and dollar amount

The file is over 100 mb.  Is there a way to make it smaller?

Regards

• ###### 3. Re: Conditional color formatting average payment term spend based on terms and dollar amount

The context for the 10-minute video in this thread is to help someone anonymize sensitive data, but the principle employed would serve you here.

Grab 100 rows from the "View Data" function in desktop.  (And maybe you could get away with 10 rows.  Just have enough to do what you need to figure out here.)  Then follow the steps to end up with an excel data source.

• ###### 5. Re: Conditional color formatting average payment term spend based on terms and dollar amount

I have reduced the size of the data file but it still has enough data to display what I want to do.

• ###### 6. Re: Conditional color formatting average payment term spend based on terms and dollar amount

See attached.  The calc is called [Color].

Note that I set the table calc settings to ACROSS THEN DOWN so that it looks at all values together.  (I could have selected DOWN THEN ACROSS for the same result.)  RANK is a table calc, so we need to tell it how we want to address the table.

And then I selected a CUSTOM DIVERGING palette where I could select red for one end and green for the other.  (There is already a stock one of those out there that comes with Tableau, actually.

• ###### 7. Re: Conditional color formatting average payment term spend based on terms and dollar amount

Joe, forgive my ignorance, but how do I get to the attachment?

Regards

• ###### 8. Re: Conditional color formatting average payment term spend based on terms and dollar amount

You get the attachment by pointing out:  "Joe, you idiot... you forgot to attach the file!!!"

It's attached to this reply now.