KNOWLEDGE BASE

Color Assignments for Today Do Not Persist When Using TODAY() Function


Published: 13 Oct 2016
Last Modified Date: 01 Nov 2016

Issue

When a calculated field on Color contains the TODAY() function, color assignments do not persist over time, but change each day when the results of the TODAY() function update.
 

Environment

Tableau Desktop

Resolution

Option 1

Use INDEX() to assign colors, and create a separate worksheet to use as a legend. See attached workbook for an example.
  1. Right-click your date field and select Create > Custom Date
  2. Enter the following, then click OK

    Name: Date at Day Level 
    Detail: Days 
    Date Value 

  3. Place "Date at Day Level" on Detail.
  4. Select Analysis > Create Calculated Field
  5. Name the field "Index". Enter the following calculation, then click OK
    index()
  6. Place "Index" on Color
  7. Right-click "Index" on the Marks card. 
  8. Select Compute Using > Date at Day Level, then click OK

Option 2 

If the different lines we want to color will be a consistent number of days from today, a Calculated Field using the DATEDIFF() function can be used to assign colors:
  1. Create a Calculated Field called "DateDiff_today = " using the following formula:
  • 	DATEDIFF('day',TODAY(), [Date periods filtered] )
    
  1. ​ Create a Calculated Field called "Datediff_with strings" using the following formula:
  •     IF [DateDiff_today] = 0 THEN 'TODAY'
        ELSEIF [DateDiff_today] = -1 THEN 'YESTERDAY'
        ELSEIF [DateDiff_today] = -7 THEN 'LAST WEEK'
        END
    
  1. Drag the "Datediff_with strings" field to the Color Section of the Marks card.

Cause

When a date result is used to color marks on a view, the colors are assigned for the resultant date, so the color for dates returned from a Calculated Field that uses the TODAY() function will change each day.
 

Additional Information

If there is missing data in the data set, use the formatting options for the Value, under the Pane tab, for Special Values (eg. Null) and Select for Marks: Hide (Connect Lines) to eliminate any gaps in the data and retain the color encoding.

By using INDEX() to color marks, you can assign colors for days such as:
  • Today
  • Yesterday
  • 1 week ago
And have these colors persist for the associated dates, even when the date for today changes.
 
Did this article resolve the issue?