2 Replies Latest reply on Jan 4, 2017 8:55 AM by Chris C

    Hide Null Measure Values

    Chris C

      Please see attached Superstore workbook.  I'm using Tableau 10.1.

       

      The table is broken out by Category vs Week(Order Date).  I want to show the percent difference for only the latest TWO weeks in the filtered time period.  I created a calculated field "Last Column Percent Difference" with the formula:

       

      IF LAST() = 0 OR LAST()=1

      THEN (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

      END

       

      It grabs the last two columns and calculates the percent difference.  My issue is the preceding weeks in the time period that do not have any percent difference values.  I would like to hide them.  I still want to keep the SUM(Sales) for every week in the time period.

       

      PercentDiffOriginal.PNG

       

      If I add the Percent Difference to the Filter pane, make sure "Include Null Values" is unchecked, it filters to only the last two columns for both Sales and Percent Difference.

      PercentDiffFiltered.PNG

      Right now, my workaround is to create two sheets.  One sheet uses only the percent difference column with the aforementioned filter.  Then, I add the two onto a dashboard with linked Date filters.  But is there a cleaner way of doing this on one worksheet?

      Here is my intended goal:

      PercentDiffGoal.png

      Bonus feature: Can I color code only the Percent Difference columns?  Negative = Red, Positive = Blue