3 Replies Latest reply on Aug 13, 2018 2:19 PM by swaroop.gantela

    Hide, not filter, null values

    Adam Payne

      Hi Everyone,


      I'm not sure if this is a simple formatting adjustment or if it is actually part of my formulas that needs to be tweaked. Basically, I want to hide/not show null values without filtering them. My data set is way too large to upload a packaged workbook, several hundred megabytes, so here's a screen shot and hopefully I can explain my situation clearly.


      If we take a look at the third result under "Household or Org Name", there is the Perkinson, Aaron and Elizabeth Household. The next column is set to show the name of the primary contact. The Perkinson, Aaron and Elizabeth Household has two contacts, one primary and one secondary. Tableau therefore returns two rows, one for each contact, but only shows the name of primary contact.


      The big issue is that it causes Tableau to duplicate all the data to the right, including gifts, prospecting statuses, activities, etc. I've noticed this behavior in other reports but this is the first one I've built where the blank lines are causing the data to be incorrectly displayed.


      Any tips or links if I've missed a knowledge article that would help me out would be greatly appreciated. Thanks!

        • 1. Re: Hide, not filter, null values



          I may not have caught the gist, but I have a feeling

          that it is going to depend on the nature of the calculated field

          creating the Primary Contact.


          Is there a field in the datasource that indicates if someone is primary vs. secondary,

          and then does your calculated field say something like:

          IF [ContactRank]="Primary" THEN [Person] END


          If not, would be grateful if you would post the text of that calculated field.

          If I am understanding correctly, you are seeking to collapse the rows

          into one per household. I made an attempt to set the Primary Contact

          as an Attribute, as you had done on other fields on the Row shelf,

          and it seemed to do the job in my overly simplistic mock up.


          If that doesn't work, you could take a maximum of the Primary Contact

          and FIX it to the Household using an LOD, or take the maximum by

          Table Calculation using a Window_Max.


          Given your description of the size of the dataset,

          the ATTR would likely shouldn't have a performance hit,

          but other two might, particularly the LOD.


          If I'm completely off base, would be grateful

          if you would adjust the attached workbook v10.3 and dataset

          to more closely match yours. They can be found in the Forum Thread.

          • 2. Re: Hide, not filter, null values
            Adam Payne

            Thanks for the help! I attached an updated test data set. The primary flag is not on the contact record at all, it's on the account record. The account has the ID of the contact that is primary listed as a related object but the contact can be primary on more than one account so there isn't a primary flag on the contact. Hopefully, that makes sense (not the set-up, but how I described it )

            • 3. Re: Hide, not filter, null values



              Thank for the test data set.


              I made the assumption that your joining the two sheets on Household ID.

              If that is the case, I think you can get it down to one row using either:

              { FIXED [Household ID]:MAX(IF [Primary Contact]=[Contact ID] THEN [Contact Name] END)}




              { FIXED [Household ID]:MAX(IF [Primary Contact]=[Contact ID] THEN [Contact Name] END)}


              The table calculation version may give you better performance, given the size of your true dataset.


              Because there are two gifts for this household, there will still be two rows if you want each of them to show.

              If needed, you could try and concatenate them using the method described here:

              Re: Motif classification


              Please see workbook v10.3 attached in the Forum Thread.