2 Replies Latest reply on Sep 24, 2012 2:53 PM by Zach Leber

    data padding to complete domains and preserve columns after filtering

    Zach Leber

      I believe I have a situation where data padding is necessary to complete a domain when using sparse or filtered data given that Tableau  domain completion is limited to dates and bins.  In this example I am showing production levels at several locations and I want to filter by type or date but not have my location columns disappear when there is no production.  I think my workaround effectively illustrates the technique while begging for improvement.  In my raw data source I appended one extra row for every location I want to make sure is always shown.  Then in the workbook my date filter is said to include null dates and my type filter must keep Null checked on.  And I created a calculated field vehicle_count = IIF(ISNULL([VIN] ), 0, 1) which effectively hides the Null data in the graph though not the legend or quick filter.  Any other ideas?

      vehciles dash 3.png

      vehicles data.png


      Message was edited by: Zach Leber - updated crosstab to show Type field


      Message was edited by: Zach Leber - added Type field to crosstab - added column header to Type column

        • 1. Re: data padding to complete domains and preserve columns after filtering
          Jonathan Drummey

          Hi Zach,


          I run into this issue regularly... Unfortunately there's no magic solution. Besides Tableau's domain padding (which is dependent on how the pills are arranged in a view and what kind of calculation is being performed), there are a couple of methods to build your own padding. The first step in the padding is to get the list of values that you need: one method is to set up a dimension table in the data source, another is to set up an aggregate query that gets the values from your fact tables. Then, that padded information can be on the left side of a left join with the fact data. I set up examples of these in the attached workbook, one where the dates (months) are coming from a dimension table, another where the types & plants are coming from the fact table.


          Where this gets fun is when you want to pad multiple dimensions at once, whether to make filtering easier and/or to always display values. In that case, I resort to a cross product to generate that, and then use that as the left side of the left join. Once you have that, then you can use a calc like you created show the measure results and take advantage of Quick Filters. That's the third option in the attached.


          Alternatively, to get rid of the Null's in the filters you can use a parameter instead of a filter and then use the chosen parameter value to filter inside the calc. The advantage to that method is that you don't have to generate quite so many extra rows for padding, the disadvantage is that it's more effort to set up and maintain, and parameters don't have the same range of options as Quick Filters (like date ranges and multi-select).



          1 of 1 people found this helpful
          • 2. Re: data padding to complete domains and preserve columns after filtering
            Zach Leber

            Thank you Jonathan, I can use that Custom SQL to do the single-dimension data padding I need.  As for getting rid of Null, I think I'll alias it to (do not remove) and try to get it to sort at the bottom of the filters and legends.  I considered a parameter but the data domain will keep growing on me and I don't want to have to re-publish with the new choices.