A sample workbook would help, I think. We use a similar highlight table that first uses a calculated field with all sorts of crazy stuff like:
(ZN(SUM([Net Sales]))-LOOKUP(ZN(SUM([Net Sales])), -1))/ABS(LOOKUP(ZN(SUM([Net Sales])), -1))
**Note: I didn't write this calculation. What I wrote above is about 20% of the total calculation. I have no idea how it actually works. Madness!
Then a discrete calculation that assigns a value of 2, 1, -1, or -2 depending on the result from the first calculation. The highlight colors are assigned based on this second value. Without looking at a sample though it's tough to determine whether or not our solution could help you out.
i've created 3 option in the attached workbook.
The first makes the first visible week relevant (ie it is red of green depending on its movement from the week before), as the % difference calculation is a table calc the prior periods data needs to be in the table, but to hide it we create a calculated field = First()=0, drag this onto the columns shelf and hide the one that says True (which should be the first period). Therefore leaving the periods you want to display but making the period prior available to the table calc.
The second option sets everything prior to your filter date to zero, therefore the table calc will show as green in the first column.
The last option sets the first column to white using a narrow colour range around 0%.
Hope one of these helps you on your way.
211562RP.twbx 397.2 KB
The last one was what I was looking for. I used this: Create a Custom Color Palette | Tableau Software to create a Red, Black and Green to get me the look that I wanted (Black first column) and then changes.
As my number is a whole, I just change the values to -1 and 1 and it took care of it.
Thanks again to both of you for responding to my issues. Appreciate it.
Thanks for the suggestion Vincent.