1 Reply Latest reply on Jul 10, 2012 9:53 PM by Jonathan Drummey

# Change color of line in a chart based on date

Good Afternoon,

I am trying to finish a dashboard but I’m having trouble with one piece.

We have been using Excel and PowerPoint for last 5 years but we’re having to spend countless hours trying to get the graph the way it looks.  The idea here is to compare two different dates and determine if there were any improvements in the Lines Handled Per Hour and/or Errors/1,000 Lines Shipped.

-Green arrow means they both improved.

-Black arrow means either Lines or Errors.

-Red arrow means they were no improvements.

To get this graph to look the way it does, I have to manually drag the arrows and connect the dots from the previous dates.  The problem is that it takes too much time to do it, (about 6 hours a month since there are 10 slides to this presentation.

I have been able to replicate the same graph in Tableau using the DATE as the Path of the Line (Please see Lines vs. Quality Improvements sheet on the attached workbook).  The problem that I have encountered is the COLOR of the line/lines.  I want to be able to select different dates and the color of the Lines to change from one previous date to a current date depending on the improment like the PowerPoint.  I have attached a copy of the Workbook and the Excel file for you to take a look at the sheet Lines vs. Quality Improvements to see if there is a way to do this.  This will show what direction each facility is going from month to another.  Please take a look the workbook and let me know if there is anything you can do to help.

THANK YOU VERY MUCH

• ###### 1. Re: Change color of line in a chart based on date

Hi,

I created a calculated field for the Month of Date to avoid Tableau padding out dates too much with the table calculations that were necessary to get the Line KPI to work. The calculation for Errors/1K lines is:

LOOKUP([Errors/1,000 Lines Shipped],LAST()) >= LOOKUP([Errors/1,000 Lines Shipped],FIRST())

With a similar calc for the Handled per Hour. Then the Line KPI is set up to return Green, Black, or Red depending on the criteria you specified. The Compute Using for Line KPI is set to Month of Date

I also created calculated fields for both the Lines Handled per Hour and Errors/1K lines that just return the latest month (Compute Using is also set to Month of Date), then added them as a double dual axis with the Shape Mark Type, using the Line KPI to set the shape. Although Tableau can't make arrow ends pointing at the proper slope of the line, we can use up and down arrows or other shapes to get an approximation and identify the end of the line (latest month).

One cool thing with this set up that I hadn't thought of before is that there's a kind of trail that shows up as you include more months in the Quick Filter. Since the calculations are looking at the first and last months of whatever is selected, the shapes and line colors automatically adjust and interesting (visually, at least) patterns show up like so:

Cheers,

Jonathan