1 Reply Latest reply on Jul 18, 2013 1:13 PM by Joshua Milligan

    Display response categories in table even if not in data

    Ian Conlon

      I just started using Tableau a few days ago, and I have a question about crosstables (though I guess it would apply to other visualizations as well). I'm relying on a dynamic SQL query to pull data from my database for different subpopulations. In my reporting of the data for each subpopulation, I would like to have identical tables to standardize the display, even if the response category in question doesn't appear in a particular data extract.


      For example, let's say I have a variable named "Fruit" that has the following response categories: "Apple," "Orange," "Banana," and "Strawberry." Some of my data extractions will have values for each of these different categories, so all 4 will show up in any visualization. Others, however, might have values only for "Banana" and "Strawberry." In my experience with the latter situation, Tableau only shows the categories that actually appear in the active data set, so the table for this person shows only "Banana" and "Strawberry." I'd like for the table in their report to also show the value labels for "Apple" and "Orange" and just have 0's or no data there. I've tried using "Show missing values" and "Show empty rows", but because the particular category isn't anywhere to be found within the extract, Tableau doesn't even register it as missing.


      How can I make sure that, regardless of what data actually exists within an extracted data set, that the tables I create all have a certain set of response categories listed? Any help would be greatly appreciated.

        • 1. Re: Display response categories in table even if not in data
          Joshua Milligan



          "Show Missing Rows" will work if the value exists within the data (even if it is filtered out), but as you noted if those values aren't present at all in the data set, they won't be displayed (Tableau has no way of knowing all the possible values of a domain -- unless it is something like a date or bin).


          What you might try doing, is including a dummy row for each possible value in the domain ("Apple," "Orange," "Banana," and "Strawberry" -- thus 4 dummy rows).  Most likely you'll use a Union, depending on your original data source.  You can filter the dummy rows out, but if the "Show Missing Rows / Columns"  options are used then you should get all values consistently.




          1 of 1 people found this helpful