5 Replies Latest reply on Feb 13, 2017 9:33 AM by Jen Shepherd

    Replaced blank data with 0, but those cells just stay white in a colored heat map (Desktop 9.3.9)

    Jen Shepherd

      Hi all,

       

      I've been browsing for a solution for a while but everyone else who has posted on this topic seems to have the opposite problem -  they want to prevent replaced nulls from shading in their viz whereas I want them to be shaded.

       

      I am looking at crime counts by year and want to replace missing data with zeroes. I tried several formulas I found online and can get the zeroes to appear in the heat map fine; I'm using  (IIF(ISNULL(LOOKUP(SUM([Offense count]),0 )), 0, SUM([Offense Count])).  But the cell color for the new zeroes is always white.   I want the Tableau-supplied zeroes to be treated like true zeroes and follow the color scheme I select.

       

      null color problem.png

      null color prob marks.png

       

      Any ideas what's happening or how to fix it?

       

      I've tried changing how the Offense Count table calc is counting (across, down, etc.) but no matter what I choose, either the zeroes in the heat map disappear (but the white cell background remains) or it looks exactly like the screenshot above where it is calculating across.

       

      Ultimately I want to apply a calculated field to the color shelf to color all the cells based on their values vs. my criteria (see my previous post:  Trying to highlight values 40% different from an average, using only 3 colors (Desktop 9.2.2) ) But that calculated field isn't working correctly either due to this fundamental "white cell" problem.

      null color problem - shaded.png

       

      The data are also filtered by a T/F parameter to choose a specific agency from a string list.  All of this is from a single data source - no joins, blends, etc.

       

      My data are sensitive so I'm hoping this is an easy problem to diagnose without a workbook.  But if needed, I can create a shareable version.

       

      Thanks!

      Jen

        • 1. Re: Replaced blank data with 0, but those cells just stay white in a colored heat map (Desktop 9.3.9)
          Joe Oppelt

          I would want a workbook to look at.

           

          Anonymize your Tableau Package Data for Sharing

           

          First thing I would check is what field you are using on your color shelf.

          • 2. Re: Replaced blank data with 0, but those cells just stay white in a colored heat map (Desktop 9.3.9)
            Jen Shepherd

            Hi Joe,

             

            I'll see what I can do about anonymizing.  Right now there is nothing at all on the color shelf.  That's the default Tableau blue for the Bar marks.

             

            Jen

            • 3. Re: Replaced blank data with 0, but those cells just stay white in a colored heat map (Desktop 9.3.9)
              Jen Shepherd

              Joe,

               

              I added a workbook.  The Basic sheet is without my own color preferences added to the color shelf.  The Highlighted sheet has the coloring added - both show the white cells for the replacement zeroes.  Thanks for taking a look!

               

              Jen

              • 4. Re: Replaced blank data with 0, but those cells just stay white in a colored heat map (Desktop 9.3.9)
                Joe Oppelt

                See Attached.

                 

                Here's the problem:  even though you forced a text value into those zero slots, there isn't an actual mark for 11C/2013, for example.  I even tried a diverging color spectrum to force an obvious color onto the zero-value blocks.  You can see that in your original "Basic" sheet.  I also played with the ZN() function (which essentially shoves a zero where there is a null.)  No luck.

                 

                So I redesigned this a bit differently.  See sheet 3.  I created a calc that keys off your parameter.  (Actually keys off your Agency Filter.)  It shoves a zero where the filter is FALSE.  Doing it that way, I no longer need the filter on the filter shelf.  It's done in the data instead.  (Oh, and I had to change the size calc to AVG instead of SUM.  Put it back to SUM and you'll see what I mean.)


                (BTW, you could just force a 1 in there without a calc.  I did that as an example where you see SUM(1).  To make that happen, double click in the white space below SUM(1) on the detail shelf.  It gives you a little edit box.  Type 1.  Hit RETURN.  I just learned that recently myself.  You can do the same on the columns or rows shelves.  Give it a try.  Make one in the columns shelf that is just "A" and see what happens.)

                 

                So now you have this grid with all the numbers added up.  I assume you don't want to see 36A because it has zeros all the way across.  So go to sheet 4.

                 

                I made a calc to sum up the total for each [IBR...].  I put that on the filter shelf and selected for minimum of 1.   If there is even one cell with at least 1 in it, then the IBR will display.

                 

                Now go to sheet 5.  This is where I was asking about what you had on the color shelf.  Now you can see that zero gets a color.  There is an actual value there.

                 

                I modified your [Highlight] calc (copy).  See Sheet 6.  I see that the average block for 11C also gets set the way I did it, so maybe the LOOKUP logic you did is necessary to keep avg from getting highlighted a different color.  You can play with that.

                2 of 2 people found this helpful
                • 5. Re: Replaced blank data with 0, but those cells just stay white in a colored heat map (Desktop 9.3.9)
                  Jen Shepherd

                  Thanks so much Joe!  I was able to use what you did and adjust the color highlighting formula to get it the way we need it.  Thanks for taking the time to solve this AND provide your reasoning, steps, and suggestions.

                   

                  Much obliged -

                  Jen

                   

                   

                  In case anyone is curious, I wanted the average column to remain un-colored so I wrapped Joe's new measure in a sum (called "SXO modified (Sum)"), then used that variable in my Highlight formula (called "Highlight outliers with zeroes").  I had to add the Sum otherwise I got "aggregation" validation errors on my highlighting formula.  We did want rows of all zeroes to appear in the chart so I based my adjustments off Joe's Sheet 3.  I've attached a new workbook with his steps still included, and with the solution I ended up with on the "final" worksheet tab.