6 Replies Latest reply on Apr 26, 2018 6:48 AM by Denis Poat

    Show default label for missing row+column intersection.

    Denis Poat

      Hello,

       

      Currently I'm working on a Dashboard and I have the following issue (it looks even worse if applying filters):

           Before applying a filter it's okay as there is enough data to fill all the gaps:

           img1.png

       

           But, if applying filter Group it looks the following way:

      img2.png

       

      I tried to use Analysis -> Table Layout -> Show missing rows and columns, but this only shows the column/row name, but not a value for all the options in the matrix.

      Also, tried to do something with ZN/LOOKUP/IFNULL but was not able to get what I need.

       

      Would be nice if someone would give an idea on how I can implement this.

       

      Thanks.

        • 1. Re: Show default label for missing row+column intersection.
          swaroop.gantela

          Denis,

           

          I'm not sure, but I think the method by Deepak in this thread may

          be helpful for you:

          Color fill all blank cells where shapes are not present

          • 2. Re: Show default label for missing row+column intersection.
            Denis Poat

            Hello Swaroop,

             

            As you can see I already used that kind of solution to fill all the NULL values with 0, but the issue appears when combinations for a whole column/row are missing. For example, in the 1st image, there are 0 records with Disposition Partial (in the image the column is filled with 0s because I created dummy data, i.e. create 5 more records with Disposition Partial and with all the Activity States, Delivery/Failed/etc.). But the solution with dummy data is ugly and I have a lot of filtering options, I would have to create like tens of thousands of dummy rows ... The issue is that, for this kind of columns, I don't event have NULLs ... the data just doesn't exist at all...

             

            Thanks,

            Denis.

            • 3. Re: Show default label for missing row+column intersection.
              swaroop.gantela

              Denis,

               

              Apologies for missing that.

               

              Just wanted to clarify: when you are referring to dummy data,

              is that a scaffold on a separate sheet/datasource

              that has every combination of State and Disposition?

              That is, are there tens of thousands of combinations of State and Disposition?

              Also, what is your type of datasource?

               

              Attached in the Forum thread is a mocked up datasource.

              Please adjust it to more closely match yours.

               

              Also attached is workbook using the scaffold method.

              267867missing.png

              • 4. Re: Show default label for missing row+column intersection.
                Denis Poat

                Hello again, Swaroop,

                 

                  My datasource is MSSQL/Oracle.

                 

                  When I talked about dummy data I meant that I create temporary tables with all the Dispositions/States/Types/etc and from their data I create one big temporary table that contains all the available combinations of the fields that will be used for filtering (the fields that won't be used for filtering are all NULL). I use UNION ALL to add this data to my Views.

                 

                  The issue is that I have a lot of filtering options (10) and some of them might have more than 10 possible options (Disposition only has 4 options and State 5, but when multiplying with 3 more options for filter A * 4 for filter B * 3 for filter C * 4 for filter D * about 15 for filter E and so on, I get a lot of dummy data/scaffold ). The client plans to use this Dashboard with tens of millions rows of data. I'm not sure about how this solution will affect the performance, as I am still new in my job of DB Dev/Data Analyst. Also, PM said that he doesn't likes this idea....

                 

                Thanks,

                Denis.

                • 5. Re: Show default label for missing row+column intersection.
                  swaroop.gantela

                  Denis,

                   

                  Sorry, couldn't make much headway.

                  I looked through the options in this post, which you've probably already gone through,

                  Force Blanks to Show.

                  but it didn't seem like it was going to work with your particular filter scenario.

                   

                  One other suboptimal attempt is attached here.

                  It unions just a table of the 20 combinations of Disposition x State

                  without any dimensions, except for a new column just to say that it is dummy data.

                   

                  Simply, every filter will check if a row is dummy data and put that into a "Baseline" option,

                  otherwise just return the rest of the filter values. And so this will need to be a

                  multi-select filter with "Baseline" always checked, plus the user selected choices,

                  and then 0s will always show. Certainly suboptimal because of having to keep the "Baseline" option clicked.

                   

                  Another method is using parameters, but suboptimal because those are not dynamic.

                   

                  Hoping others in the community will have other ideas.

                  1 of 1 people found this helpful
                  • 6. Re: Show default label for missing row+column intersection.
                    Denis Poat

                    One more hello, Swaroop,

                     

                      This is a very nice idea. The only thing that don't let me to use it (in my Dashboard) is the fact that the only way (I found) to exclude a filter option from final user (but still keep its data on the sheet) is to use a parameter (as in this example Tableau - Filter has one value always selected - Stack Overflow ). But, the parameter only allows to select one value at a time, but I need user to be able to select multiple options, at the same time.

                     

                       I really appreciate the ideas you shared!

                     

                    Thanks,

                    Denis.