1 2 3 Previous Next 61 Replies Latest reply on Feb 20, 2020 7:23 AM by Claudia Rosen Branched to a new discussion.

    Force Blanks to Show.

    Shawn Wallwork

      Here's one of those odd little things you first think will be easy, but then....


      In the attached file I set up this viz:




      (20 columns showing). My client wants to filter down to Australasia without losing any columns. But applying the filter produces this view:




      (15 columns showing). We've lost all the columns without data. I have tried various solutions involving:





      zn(lookup(sum([Sales]), 0))


      Nothing is working. Anybody have any other ideas? Or is this just something simple that I'm not seeing?



        • 1. Re: Force Blanks to Show.
          Jonathan Drummey

          Hi Shawn, no workbook? It must be because you're working with a text table.


          What's happening is that in the original view, based on your table layout Tableau is performing domain completion - in other words, creating a cell for every combination of Department, Container, and Continent. Tableau checks the domain (think of it as range of values) and shows Abc where there is at least one row in the data source for a given combinatation of Department, Container, and Continent, while showing an empty cell where there are no values, like Furniture/Large Box/Australasia.


          When you filter for just Australasia, there is no Furniture/Large Box for any Continent in the data, so Tableau doesn't draw it at all.


          The reason why the various calcs you tried didn't work is that Tableau computes row-level and regular aggregate calculations in the data source. When there's no data, there's no result, so a basic ZN(), IFNULL(), etc. wouldn't return anything, not even Null. Even though SUM([Sales]) would return no result in the original view, the ZN(LOOKUP(SUM([Sales]),0) trick would work to populate an empty cell because domain completion has padded an empty cell and the LOOKUP() table calculation will then operate on that to return Null to the ZN() function. When just Australasia is in the view, there's no data, no cell, and no padding going on to let any of htat work.


          There are a couple of options that I know of to pad in those columns:


          • Turn on Analysis->Table Layout->Show Empty Columns. This will make Tableau fill in the crosstab, but in most cases you wouldn't be able to get Tableau to show anything in those cells. The kind of padding done by Show Empty Columns and Show Empty Rows is mostly not addressable by table calculations, not fillable by Field->Pane tab->Special Values->Text, etc., it's just for visual display.
          • Use a custom query or custom SQL to pad out your data to include those missing values. This will create extra records, so you'd have to be careful using a calculated field instead of just SUM([Number of Records]), but will work.



          12 of 12 people found this helpful
          • 2. Re: Force Blanks to Show.
            Shawn Wallwork

            Guess I need to read that doc we wrote again. Workbook now attached to original.



            • 3. Re: Force Blanks to Show.
              Shawn Wallwork

              Turn on Analysis->Table Layout->Show Empty Columns.

              Thanks Jonathan. I think this is what they're looking for. Now, we'll have to see how it works in the real data. Appreciate the help. Sometimes it's just the simplest thing.






              EDIT: BTW, they don't want to populate the cells, they just don't want to lose the columns. Thx.

              4 of 4 people found this helpful
              • 4. Re: Force Blanks to Show.
                Joe Mako

                Attached is another method in addition to those listed by Jonathan that uses table calculation to perform densification and filter.

                5 of 5 people found this helpful
                • 5. Re: Force Blanks to Show.
                  Chris Sommers

                  Hi Joe,


                  I have a similar objective but your technique doesn't seem to work for it.


                  I have columns with Month / Year names.  I want to show all the months for a single year (whether or not they had values tied to them).


                  I place the newly created variable Year = WINDOW_MAX(ATTR([Fiscal Year Num])) in the filter and select a specific year.  I then go to Analysis->Table Layout->Show Empty Columns.  All the columns show and the filter does not filter down to the Month / Year of the Year filter that I've just applied.  Any suggestions?



                  2 of 2 people found this helpful
                  • 6. Re: Force Blanks to Show.
                    Andrew Carlson

                    I have the same issue as Chris above, and its driving me bonkers!!!!!!!!!!! Any help would be greatly appreciated!

                    • 7. Re: Force Blanks to Show.
                      Joe Mako

                      Chris and Andrew, can you provide a packaged workbooks that represents your situation? (does not need to be real data, just need your data structure and pill arrangement as a starting point)

                      • 8. Re: Force Blanks to Show.
                        Andrew Carlson

                        Hi Joe,


                        First of all, sorry for the delay in responding - I got caught up in something and had to put this on the back burner. I can do my best, see the attached. I had to rebuild the situation with the SuperStore, but I think it indicates my particular situation (I cant speak for Chris)


                        We have a client who receives orders for certain types of products, and wants a viz that will allow them to drill down by type of product AND by "month received", with the goal of seeing the average number of that type of item ordered during a specific month - so for a 5 year data set filtered to the month of "March", we'd hopefully have 5 data points: one totaling the number of that particular type of item ordered during the month of March for each year of the data set. Eventually we'd get average orders for "March", and be able to trend and compare new months to the average.


                        However, we've come across the issue where certain item types weren't ordered at all during a specific month. We'd like to show a 0 value for that month, which is how I found this forum posting. While the data set has no record, the knowledge that nothing was ordered (i.e. a 0 value) is still useful for my client - further, getting a point of any type for a missing month is necessary for the average calculation (as we want to be averaging it by the full 5 years vs only the total number of years with data).


                        So, for the attached workbook, I'm filtering by "Envelopes" (our "item type") and "Shipping Month" - our "monthly" date variable.


                        By instituting the logic of nesting the IFF(ISNULL(LOOKUP), I can get the data to display 0s for null records - in the example, take a look at the month of March 2011 to see this in effect. This calculation, aptly titled "Calculation", is my "Rows" dimension.


                        I've got the graph set to "show missing values", which is what allows the "March 2011" value to display. However, try to filter the graph to only show the month of March (by using the quick filter).


                        Even though the filter should only be displaying the "March" data points, because the graph is set to "Show Missing Values", it appears to "overwrite" the filter setting, and shows me all the 0 values for the filtered months. The ideal here would be to only show the 0 value for March, as thats the criteria I'm filtering off of.


                        If I take off the "Show Missing Values" option, it only shows data points for the month of March, but it excludes the "0" value for March of 2011.


                        This is a very simple example, but I think it illustrates my issue perfectly. I need to be able to see "0" values for items when a filter is applied, which I can't seem to make it do in this circumstance.


                        Any assistance you, or any other wise members of the forum could provide would be outstanding, as this seems like it would be a common issue.


                        Oh, one other thing, due to the nature of the data and the users involved, a solution within the database is not possible - i.e. scaffolding or creating a base table. The solution has to come from within the application


                        Thanks again!

                        2 of 2 people found this helpful
                        • 9. Re: Force Blanks to Show.
                          Chris Sommers

                          I've add a second sheet to Andrew's workbook to demonstrate what I'm trying to achieve.  I would like March 2011 to display in the Viz with a value of zero.





                          • 10. Re: Force Blanks to Show.
                            Joe Mako

                            How about a filter on a calc field like:


                            DATE(DATEADD('month',INDEX()-1,TOTAL(MIN(DATETRUNC('month',[Ship Date])))))


                            see attached for an example, made in 8.1 as your attachment was.


                            This calc field I added will effectively fill the month values in your densified data. By using the "Show Missing VAlues" feature, you are turning on data densification, and data densification happens after the query results are returned to Tableau. Your original filter on a dimension is evaluated in the query to the data source, before data densification, while a table calc is evaluated after densification, and a filter on a table calc after all table calcs have been evaluated. Be aware that densification not not make values available, densification simply adds marks, like records with null values in every field.


                            So by filtering on a table calc that has the month value filled we can get the interaction you are asking for.


                            I'll break it down from the innermost outwards:

                            - DATETRUNC('month',[Ship Date])

                            Floor the data value to the first of the month.

                            - TOTAL(MIN(...))

                            Get the minimum, the first, month in the view

                            -  DATEADD('month',INDEX()-1,...)

                            Take that min month and add 1 month based on how far away we are from the first/min month

                            - DATE(...)

                            Change the data type from DateTime to Date, for easier default formatting.


                            I also added another view, that may support your goal, and I cleaned up your calculation to use the ZN() function.

                            • 11. Re: Force Blanks to Show.
                              Andrew Carlson

                              Holy **** Joe, you're the man. I think this'll work. I'll go ahead and try it with client data now.


                              Thanks a million!

                              • 12. Re: Force Blanks to Show.
                                Joe Mako

                                Chris, simply right-click on the pill on the Columns shelf, and select "Show Missing Values" This will turn on the Domain Padding variation of Data Densification, adding the mark for the missing month. See Tableau Request Live - Data Densification on Vimeo


                                I also cleaned up the calculation so it is:

                                ZN(LOOKUP(SUM([Count of Records]),0))


                                attached is the workbook in v9, as your attachment was.

                                1 of 1 people found this helpful
                                • 13. Re: Force Blanks to Show.
                                  Ankur Anwekar

                                  Joe Mako Fantastic work Joe. However I am facing a different issue. Using the same data what if I just want to a show a month, lets say July 2011 and still keep all the categories? When I do this, I miss out on "Bookcases" since it has "0" value in July 2011. How to keep those categories with "0" values for a particular month?

                                  • 14. Re: Force Blanks to Show.
                                    Chris Sommers

                                    Thanks Joe!

                                    1 2 3 Previous Next