8 Replies Latest reply on Mar 29, 2017 8:33 AM by Ben Riley

    How to retain 'null data' columns when a filter is applied?

    Laura Vecchio
    Laura Vecchio Feb 14, 2017 1:00 AM

      Good Morning!

       

      I have a set of data which is using a relative date filter of the previous 6 months.
      I want to add a 'Company' filter so users can select between numerous brands however when this filter is applied, the months with 'null' values disappear, even though they're visible when no Company filter is applied.

      I've tried selecting Analysis >  Table Layout > Show Empty Columns but this pulls through all the months from our source data dating back to 1993!

       

      I can't attach a packaged workbook as it's highly sensitive data from a listed company which I know makes answering my question more difficult but I'm hoping someone spots this that knows the answer or has encountered this problem before?

       

      This is the data before the 'Company' filter;

       

       

      And after;

       

       

      And once 'Show Empty Columns' is selected;

       

      etc. etc all the way through to Feb-17

       

      Any help would be hugely appreciated as I can't publish this workbook without the Company filter capability and I feel as though I've exhausted all my knowledge

       

      Many many thanks!
      Laura

        • 1. Re: How to retain 'null data' columns when a filter is applied?
          Nipun Garg
          Nipun Garg Feb 14, 2017 1:20 AM (in response to Laura Vecchio)

          Hi

          This happen because your data set doesnt contain values of those month for that company

          first make a parameter for company name then

          You should have to create a field which state that  if parameter = company then amt else 0 end

          Hope it works

          • 2. Re: How to retain 'null data' columns when a filter is applied?
            Laura Vecchio
            Laura Vecchio Feb 14, 2017 2:10 AM (in response to Nipun Garg)

            Hi, Nipun!

             

            Thank you very much for your advice, I definitely feel like it could work but it's still not quite right.

             

            A few questions if I may?

             

            a) does a parameter allow multiple value selection?
            My parameter is set up as follows with the 'allowable list' added from the 'Company Name' field.

             

             

            b) It's now showing all columns which is brilliant but it's also displaying ALL data rows even those which don't relate to the selected parameter and it looks pretty chaotic;

             

             

            c) I'm trying to force ZN's in the calculated field (so blank cells display as '0' and therefore colored red) however I'm struggling due to aggregation.

             

            IF [Parameters].[Company Name] = [Company Name (System Control)]

            THEN ZN(LOOKUP(1/[Exchange Rate]*[Placement Fee]),0)

            ELSE 0

            END

             

            My original AMOUNT field before I added the parameter was calculated by;

            ZN(LOOKUP(SUM(1/[Exchange Rate]*[Placement Fee]),0))

             

            But this obviously doesn't work as part of an IF statement.

             

            I'm beginning to think the report is impossible

             

            Thank you for your help!
            Laura

            • 3. Re: How to retain 'null data' columns when a filter is applied?
              Nipun Garg
              Nipun Garg Feb 14, 2017 2:41 AM (in response to Laura Vecchio)

              Hi

              Currently Tableau not Support multi selection in parameter.

              For another query  : you can use filter for 0 values

              • 4. Re: How to retain 'null data' columns when a filter is applied?
                Nipun Garg
                Nipun Garg Feb 14, 2017 2:49 AM (in response to Nipun Garg)

                please use

                Sum(IF [Parameters].[Company Name] = [Company Name (System Control)]

                THEN ZN(LOOKUP(1/[Exchange Rate]*[Placement Fee]),0)

                ELSE 0

                END)

                • 5. Re: How to retain 'null data' columns when a filter is applied?
                  Laura Vecchio
                  Laura Vecchio Feb 14, 2017 2:55 AM (in response to Nipun Garg)

                  Thank you for your help, Nipun. Unfortunately however none of these solutions work!

                  I appreciate your time though

                  • 6. Re: How to retain 'null data' columns when a filter is applied?
                    Tharashasank Davuluru
                    Tharashasank Davuluru Feb 14, 2017 3:25 AM (in response to Laura Vecchio)

                    Hi Laura,

                     

                    Did you try this Go to Analysis >Table layout> Show columns with no data or Analysis>Table layout>Show rows with no data.

                     

                    Thanks,

                    Tharashasank

                    1 of 1 people found this helpful
                    • 7. Re: How to retain 'null data' columns when a filter is applied?
                      Tharashasank Davuluru
                      Tharashasank Davuluru Feb 14, 2017 3:30 AM (in response to Laura Vecchio)

                      Hi laura,

                       

                      Please go through this thread  Re: Force Blanks to Show.  and check the response of Mr.Jonathan Drummey hehas explained some workarounds.

                      if it did not answered your query please let us know.

                       

                      Thanks,

                      Tharashasank

                      • 8. Re: How to retain 'null data' columns when a filter is applied?
                        Ben Riley
                        Ben Riley Mar 29, 2017 8:33 AM (in response to Laura Vecchio)

                        Hi Laura

                         

                        All you need to fix this problem is a new calculated field and the power of 'context' filtering!

                         

                        I had a problem where I needed to show only the latest data for various metrics for various organisations but some organisations had no data for certain metrics whereas others did. This was fine for each organisation, but when changing organisations using the filter I'd get a different list of metrics per organisation whereas the client required it to be the same list everytime whether there was data or not. In the end I tried 'Analysis->Table Layout->Show Empty Rows' but this showed everything. The solution was to create a new calculated field to filter things and 'add to context', this replaced my original field filter. So in my case my new calculated field was:-

                         

                        IF [Is Latest Data] = 1 OR [Is Latest Data] = NULL THEN 1 ELSE 0 END

                         

                        So it would return '1' if it was the latest data or empty, but crucially it would return 0 if there was data but it was not the latest data. In your case I guess it would be something similar, maybe not 'latest data' but rather 'appropriate dates' or whatever you're filtering for time period, but this should (hopefully) sort the problem.

                         

                        Note that if there are any other fields bringing missing values back you'd need to apply the same logic to them and create a field for them, or filter them all in 1 big calculated field - just extend the above calc. This should be obvious as when you apply the first new filter you'll see if there's loads of other data still showing, just keep adding to this new field until you're filtering out what's not required.

                         

                        Hope that helps

                        2 of 2 people found this helpful