4 Replies Latest reply on Jul 29, 2012 8:56 AM by John Liska

    Heat map treating NULLs as zeros

    John Liska

      I have a heat map that captures a range of values from single digits to ~1000. I'm using 100 as the midpoint within a red-green diverging color scheme. The issue I'm having is that this heat map has a number of NULLs (which is expected) that are all treated as 0, making them the most red cells in the heat map because 0 is the lowest value in the range. (In the example below most of the cells in the B12 column exhibit this behavior.) I would expect that Tableau would treat these NULLs as NULLs and leave the cells completely blank (i.e., white).

       

      I even created a calculated field to try to force the color coding to recognize NULLs but it yielded the same result. Is there a setting somewhere that I need to correct in Tableau to leave NULLs as NULLs? I tried the Format option for Pane and reviewed the "Special Values (eg. NULL)" options but those made no difference.

       

      NOTES:

      • The NULLs are not cells in the underlying table that are NULL; these represent rows that do not exist in the table. For example, with the first row of my example below (the one that ends in "OWERS") there is no B12 entry.
      • both the x- and y-axis are not picked directly from the Dimensions; these are parameters that are passed in by showing the parameter control.
      • The one measure that is shown is a calculated field.

       

      Any thoughts would be appreciated, thanks.

       

      JPL

       

      Capture.PNG

        • 1. Re: Heat map treating NULLs as zeros
          John Liska

          Yeah, I tried to cheat with just the screen shot. Since I can't share the actual data I have to create dummy data and re-create the worksheet; kind of a painful process.

           

          I'm attaching an example workbook as well as the underlying Excel dummy data I used. I'm fairly certain the structure of the hierarchies is confusing Tableau somehow but I don't know why and I'm not sure how to correct it. It's not ideal but this is how the data is delivered to me from a SAS modeling process, just trying to make it work. As you can see in the example workbook the same problem is occurring. I'm using a calculated field as the lone measure, and using the red-green diverging color scheme. The mid-point is a somewhat arbitrary 10% (0.10) but it shows the same behavior: the NULL cells are treated as 0 and therefore have the darkest red coloring. These fields should be empty (i.e., white). The user controls on the right-hand side allow you to change:

          • The attribute (either Category or Size)
          • The measure (either Units or Dollars)
          • The hierarchy level (Level 1, Level 2, or Level 3)

           

          Let me know if this helps and if there are additional questions. Thanks.

           

          JPL

          • 2. Re: Heat map treating NULLs as zeros
            John Liska

            I doubt I ever would have found that. Works great, thanks for the help Shawn!

             

            JPL

            • 3. Re: Heat map treating NULLs as zeros
              Joe Mako

              Shawn, that is a different computation result than what John provided.

               

              The compute using prior to using the Show Me button was Table Across, or User - Hierarchy.

               

              Your settings that you suggest is effectively Table Down and Across.

               

              The real situation that John has experienced here, is the result of using the Show Me button with a table calc pill in play.

               

              When you use the Show me button, Tableau 'locks' settings in, both enabling views not able to produce in the user interface, and causing unforeseen results like this situation, where the data is padded.

               

              I believe a better solution is to remove the table calc pills from the shelves after performing the Show Me, and then placing them again. This way you do not get the side-effect of the Show Me impacting your results.

               

              See the attached, each sheet i added is one step in the process that I would recommend.

              2 of 2 people found this helpful
              • 4. Re: Heat map treating NULLs as zeros
                John Liska

                Thanks Joe. I had noticed the table calc changes/errors after I went through some testing and wasn't sure if I had been doing something wrong when building this out. Your answer helps a lot; I didn't realize the Show Me feature had this effect.

                 

                I tried implementing this on my end (in my production version) and didn't come out with the same results. I see very clearly where after I use the Show Me button and then remove the table calc pills that I get Tableau to start behaving correctly: there are NULLs (uncolored) in cells where there are no corresponding rows in the data set, and because the Size shelf is still populated with "One (generated)" I see the remaining cells are colored with the default blue. But when I add back the table calc field to both the Color and Label shelves I get the same behavior as before: Tableau is forcing the NULLs to zero. (Plus my table calc is wrong.)

                 

                No need to keep digging in, your example works perfectly so I just need to spend more time understanding that version and applying it to my production version. Thanks again for the help.

                 

                JPL