3 Replies Latest reply on Feb 18, 2016 10:20 AM by Ben Page

    Trying to highlight values 40% different from an average, using only 3 colors (Desktop 9.2.2)

    Jen Shepherd



      I'm new to Tableau and am working on a dashboard that includes vizes from three different data sources.  Each source contains several metrics (e.g., crime counts) for 400+ agencies, with agency name (a string var) consistent across all the data sources.  I want to look at the metrics by year in a heat map with the metric values as labels.  I  want to compare the most recent year (2015) to the average of the previous 4 years (the average is a column in my data files).  I also want to present this info on a dashboard while filtering the data to see all metrics for only 1 agency at a time.  I created a parameter to select agency name so that I could use it across all vizes on the final dashboard.


      As an example, I want to show a yearly count of crimes and arrests for Agency A, and compare every year to the 4-year average for each crime and arrest type, highlighting with color any changes by year that are "significant" (operationalized  as < or > 40%).


      I first used a quick table calculation (% difference from) on the crime and arrest variables  - computed using [Year], Relative To the 4-year average .  I set the color mark on the table calculation to the orange-white-blue diverging palette, then set it as reverse 3-stepped and set the end colors to be red (positive % difference) and blue (negative % difference), with white in the middle  (no change).  However, when I filtered by agency name on the final dashboard, there were  colors showing from the initial palette other than red, white, and blue for some of the agencies.  Specifically, when the overall %change range is lopsided, it brings in other colors that are midrange.   For example, an agency that has a range of -50 to +500 %change has color marks that are orange or light blue.


      I submitted this problem to Tableau Support (twice) and they were able to create a calculated field that almost does what I need.  However, the KPI approach colors any value that is not *exactly* the same as the reference value (4-year average) whereas I only want to highlight changes < or > 40%.


      Here's my understanding of the steps Support took:

      +Created a calculated field (!Test KPI) that defines the values for the different colors.

      +Place the new KPI field onto color

      +Edit the color table calculation to be computed using Year and Relative to 2011-2014 avg.

      +Change the chart type to Bar

      +Create a new calculated field called "One" that is just the value 1.

      +Place the One field onto the Size shelf and adjust the size to the largest setting. Adjust the settings according to the article on conditionally formatting cell background color.


      This is the first version of the !TestKPI formula Tableau Support created ("Actual" is the column name of the crime count metric):


      IF (ZN(SUM([Actual])) - LOOKUP(ZN(SUM([Actual])))) / ABS(LOOKUP(ZN(SUM([Actual]))))<0 THEN "Blue"

      ELSEIF (ZN(SUM([Actual])) - LOOKUP(ZN(SUM([Actual])))) / ABS(LOOKUP(ZN(SUM([Actual])))) >0 THEN "Red"

      ELSEIF LOOKUP(ZN(SUM([Actual]))) = 0 AND SUM([Actual]) >0 THEN 'Red'

      ELSE "White"



      Support  also mentioned "The KPI calculation can be adjusted to be greater or less than values you deem as significant.  One thing to note, the last line of the calculation was used to address some of the fields that were not were not shading as I expected. Because the 2011-2014 avg value is sometimes 0, this ended up being the denominator of the percent difference calculation, creating NULL  values."


      I was told to change the  > and < zeroes to .40 in the !TestKPI formula to achieve what I want, but that leads to all values being colored blue. 


      This is the second Support KPI formula:


      IF (ZN(SUM([Actual])) - LOOKUP(ZN(SUM([Actual])))) / ABS(LOOKUP(ZN(SUM([Actual]))))<0 THEN "Negative%"

      ELSEIF (ZN(SUM([Actual])) - LOOKUP(ZN(SUM([Actual])))) / ABS(LOOKUP(ZN(SUM([Actual])))) >0 THEN "Positive%"

      else "0"




      Tableau Support also suggested creating a custom palette, but I'm having trouble getting my Preferences file to show up and be accessible, even when granted admin rights.  (Side note- I'd love help with this issue as well.  Our IT staff haven't a clue).


      preferences problem.png



      So, my question:  can someone please help me adjust the KPI formula to only color < or > abs[40%] change? Or if you know of another way to achieve the desired end result, I would be most grateful. I've attached a packaged workbook that is a simplified version of what I'm working on.  The sample only includes one data source of fake data since my actual data are sensitive.  For simplicity and anonymity, I replaced agency name with a numeric value.   There are three worksheets reflecting the three different methods:  my Original %diff table calc, the KPI Support made, and the adjusted Support KPI using .4 instead of zero.   The "Experiment" worksheet is a work in progress where I'm trying to figure out the formula for myself (and failing).

      The Dashboard puts the results from each worksheet side by side with the agency filter controlling all three.  AgencyID 76406 is an example with colors other than the red and blue through my Original method.


      Thanks for reading, and thanks in advance!  We will be using the dashboard to send agency-specific reports and are quite a bit behind schedule to get them out.