3 Replies Latest reply on Jun 17, 2013 1:32 PM by Jonathan Drummey

    Missing values in dimensions

    Alex Kerin

      In the example attached I have two customers purchasing items that are in sub-categories. There is some overlap between the sub-categories that the customers buy from, but neither buy something from every category. In the view as shown, I would like all the sub-categories to show, even if there is no data for that customer for that sub-category. In other words, show a zero.


      If I rearrange the pills I can make the zn(lookup(sum[Number of Records]),0)) method work, but that won't provide the view I am creating. The suggestions from a number of results from here (Let me google that for you- Link to search) suggest I may need custom SQL or possibly a blend.


      There is also an Idea out there which seems to suggest what I want: http://community.tableau.com/ideas/1062


      So, question, can I do this without padding the data via custom SQL? If I cannot, what would that SQL look like in this case?


      BTW, my real example is a pipeline type report, so I need to show work as it flows down through workflow categories. The data already has a left join to another source, but hopefully that won't affect this.

        • 1. Re: Missing values in dimensions
          Jonathan Drummey

          Hi Alex,


          There is already an Analysis->Show Empty Rows/Columns option, but that only pads the domain based on the underlying data for the given dimensions. Those two customers don't have any rows for the missing sub-categories in the superstore sales data, so Show Empty Rows does nothing for them. There's likely some wacky solution using domain completion and crazy pill arrangements, but that would overcomplicate all further calculations.


          One option is to create a scaffold data source that includes all dimensions at the necessary level of detail, so you'd build a source that has every Customer Name/Sub-Category combination. Then you can use that scaffold source as the primary in the blend. However, that can get a little funky if you're trying to do certain combinations of filters and blends at different levels of detail than the view. More on how to dynamically build a scaffold at the bottom of this post.


          Alternatively, you can pad out the data so there are extra rows in the data source for all of the combinations. This is the combination I use more often, because I'm comfortable writing SQL and making this dynamic. The only caveat is that Number of Records is no longer accurate because the data is padded out, I'll use a different calc for that. What I do for this solution is to:


          1. use a SELECT DISTINCT to get the list of customers

          2. use another SELECT DISTINCT to get the list of sub-categories

          3. create a cartesian join between them

          4. UNION the original data source to the cartesian join


          Steps 1-3 can all be wrapped inside a sub-query, as in the attached. Also, you could use steps 1-3 to build the scaffold data source.



          • 2. Re: Missing values in dimensions
            Alex Kerin

            Interesting - I had guessed the scaffold source would be the best answer but I don't have the SQL chops for creating it. I certainly would not have thought of the dynamic creation of the scaffold which makes this very neat for sources that change even infrequently. This puts my aversion to the custom SQL scaffolding to bed. Thank you Jonathan.

            • 3. Re: Missing values in dimensions
              Jonathan Drummey

              You're welcome!


              Since you're working with SQL and JET, here's a bit on my process for dealing with JET's horrendous lack of informative error messages: I'll use very small steps, testing each sub-query on a separate (duplicate) data source, starting with the lowest-level sub-query and working my way up the tree of sub-queries from there until I have the final data source ready. Otherwise a misplaced comma or parentheses can make me bang my head on my desk in frustration.

              1 of 1 people found this helpful