1 2 3 Previous Next 30 Replies Latest reply on Mar 5, 2019 6:01 AM by William Ellis Go to original post
      • 15. Re: Displaying 0 in null fields
        Jonathan Drummey

        FYI, the way I usually deal with this situation (where I always want to

        show a value no matter the underlying data) is to pre-process the Tableau

        data data source to pad it out in advance of Tableau so that way there's at

        least one record for every combination of dimensions that I need (in fact

        right now I'm taking a break from building a query to do just that).

         

        Jonathan

         

         

        On Thu, May 5, 2016 at 10:36 AM, Namrata Sawant <

        • 16. Re: Displaying 0 in null fields
          Matt Lutton

          Another great thread response from Mr. Drummey -- love it!

          • 17. Re: Displaying 0 in null fields
            Sara Bradberry

            I'm wondering if anyone has ever seen the data in your table duplicate when you add in the Null Lookup.. Once I added that in, I am able to get my table to show 0 where there previously was no data, but then all of my rows duplicate numbers.  I am unable to attach the book I am working on as it is proprietary information to my company, but something like this....

             

             

            Before:

            Department     In     Out

            A                    10     1

            B                    10 

            C                    20     1

            D                    15     5

             

            After Adding in Null:

            Department     In     Out

            A                   10     1

                                 10 

            B                   10     0

                                 10     

            C                   20    1

                                 20

            D                  15     5

                                 15

             

             

            beforee.png

             

             

            after:

            AFTERR.png

             

            You'll notice that I now indeed have 0's but all my data in the rows are duplicated.  My Null Lookup uses the formula LOOKUP(COUNT([Dimension]),0) but I also noticed in the calc box it says "results are calculated along table (across)" and I'm wondering if that has something to do with it?  I am newer to Tableau and realize this may be vague but appreciate any help I can possibly get!

            • 18. Re: Displaying 0 in null fields
              Danny Kim

              Sara - did you ever get a response? I have the same issue.

              • 19. Re: Displaying 0 in null fields
                Jonathan Drummey

                Hi Danny (and Sara),

                 

                The information provided wasn’t enough for us to give an answer… We’d need to see at least a screenshot of the view (including Rows, Columns, Pages, and the Marks Card) and know the addressing (compute using) of each table calculation.

                 

                Jonathan

                • 20. Re: Displaying 0 in null fields
                  James Davis

                  There's actually another way to do this, if adding table calculations is not something that you want to do.  What I did was as follows, assuming the following: [field2] <-calculated from [field1] <-calculated from [field] or expression on [field].

                   

                  (1) use either IFNULL(sum([field]), 0) or ZN( sum([field]))--or IFNULL([field]) or ZN([field]) if the expression isn't an aggregate computation--in generating the calculated field [field1] as part of the expression assignment (perhaps embedded in some IF THEN ELSE logic, for example); and then,

                   

                  (2) instead of directly using this calculated field [field1], create a 2nd calculated field, [field2], which references the calculation of the 1st one, [field1].  It is the calculation of [field2] that will be the one used in the data rendering (tabular, graph, etc.), rather than the primary calculation of [field1].  It is in this calculation of the 2nd field where I placed the IFNULL([field1], 0) as the sole content of the calculation for [field2].  Then, instead of using the original [field1] in rendering, use [field2].  This displays zeroes instead of empty cells.

                   

                  Note that the use case for this is forward-looking values, such as rendering Current Year Financial/Sales/Widget numbers, Jan - Dec, where in any given current viewing date (say, controlled by a month/year based Parameter drop down menu), you don't have any data available to render a valid value.  For users, empty cells are not acceptable; therefore, you need to be able to display zeroes instead--a more user friendly rendering.

                   

                  It may well be that the use of IFNULL(), ZN() functions on [field] to calculate [field1] may not be necessary, and the significant factor is the use of IFNULL() against [field1] to generate [field2]. I didn't test that aspect of the solution.

                   

                  I'm using Tableau 10.x for this. Maybe this didn't exist in older versions.

                  Cheers.

                  • 21. Re: Displaying 0 in null fields
                    Jurij Fedorov

                    I really need a solution for this too. Has anyone discovered a solution? My problem is that I can change some of the columns to not show "Null" in "Edit Alias...". But then I reload the spreadsheet with changes in it and it goes back to showing the "Null". Also, it doesn't work for all columns.

                    • 22. Re: Displaying 0 in null fields
                      Hari Ankem

                      Can we look at your packaged workbook to understand what you are doing and see if someone can help?

                      • 23. Re: Displaying 0 in null fields
                        Berton Harrah

                        I'm somewhat new to Tableau, so I don't know that this will solve your specific issue, but rather than creating calculated fields, etc, I just went to the the measure values, clicked "Edit in Shelf", then encapsulated the aggregation with a couple of the recommendations from this thread and it worked (a million thanks to those folks!):

                         

                        Original: SUM([SALES])

                        Altered: ZN(LOOKUP(SUM([SALES]),0))

                         

                        This has worked for all of my worksheets so far and I only have to edit the formula in that one place.

                         

                        Note: if the name of that measure value appears in the worksheet I then I do have to use the "Edit Alias" function so that it isn't showing that entire calculation.

                        • 24. Re: Displaying 0 in null fields
                          Renada Guidry

                          Greetings Everyone!

                           

                          I am a first time participant in the community and have found that I am experiencing this issue as well!  Particularly when using additional marks on color.

                           

                          It appears that even though you are forcing the 0s using ZN or Lookup, when a color measure is added the value is still treated as "Null" and will negatively impact your visual.  Please see the screenshots I've provided.

                           

                          The Rate calculation provided is using the ZN function, but I have also tried using the methods above (i.e. LOOKUP, using a 2nd field to reference the 1st calculated field).  The color measure is needed to identify whether the value calculated falls within Target, Threshold, or Distinguished.  I had to add the same measure used for the color mark to the rows or column shelf to identify the issue.

                           

                          The desired goal is to use the color mark with the provided ZN or look-up functions so that it keeps the integrity of the calculation. Since this calculation is aggregated over a date range, the issue only occurs for those selections that had months of null data (even though it is truly 0). 

                           

                          I apologize in advance if I have broken any community posting rules, but any insight is much appreciated!

                           

                          Before Color Mark

                          Before Color Mark.jpg

                           

                          Color Mark Added

                          Color Mark Added.jpg

                           

                          Mark added to Color and Row Shelf identifies what is causing the issue

                          Color Mark Added Plus Row Shelf to identify Issue.jpg

                           

                          Benchmark Color Calculation (I am wondering if there is something that can be corrected within the calculation) or if this is a caveat of using the ZN/LOOKUP function).

                          Color Calculation.jpg

                          • 25. Re: Displaying 0 in null fields
                            MANOJ PRABHAKAR

                            Hi i have data for jan,feb,may,apr,may,oct,nov of 2018 .When i select the option of show missing values i get the months jun,jul,aug,sep as well then using the above mentioned lookup calc i'm showing null values as 0.In the same way i have to show for the month of December, but december is missing ? The client wants to show all the 12 months with/without data

                            • 26. Re: Displaying 0 in null fields
                              Jonathan Drummey

                              Hi Manoj,

                               

                              Sorry for the late reply on this. Tableau's data densification features (of which Show Missing Values is one) only pad within the range of dates that are in the data. Therefore you're going need to do some padding outside of Tableau to get the desired results.

                               

                              Regards,

                               

                              Jonathan

                              1 of 1 people found this helpful
                              • 27. Re: Displaying 0 in null fields
                                MANOJ PRABHAKAR

                                Yes I understand Thanks Jonathan

                                • 28. Re: Displaying 0 in null fields
                                  William Ellis

                                  Hi Jonathan,

                                   

                                  Thanks so much for your enlightening posts on this topic (also Joe Mako's). Your detailed clarifications are so helpful.

                                   

                                  Not sure if this is a "special case" of data densification or is covered by the rules/principles you describe - however, I don't know how to force densification on in this common situation.

                                   

                                  The attached workbook is an example. In my crosstabs I regularly use the "Dummy Measure" workaround to produce axes that allow flexibility in formatting. When I do this, I can't seem to get densification to work. Any thoughts appreciated. Thanks!

                                   

                                   

                                  Best,

                                   

                                  Bill Ellis

                                  • 29. Re: Displaying 0 in null fields
                                    Jonathan Drummey

                                    Hi,

                                     

                                    Your view is densified…if you right-click in the whitespace in the view and choose View Data… you’ll see 4 rows. You can also see the densification if you remove the MIN(0) Dummy Measure pill.What’s happening is an order of operations issue: In the view you have a continuous axis with the Dummy Measure that has a value of MIN(0), which is a regular aggregate computed before densification, so since Tom/Feb 28 doesn’t exist in the data there is no value for the Dummy Measure so Tableau doesn’t plot anything at that location.

                                     

                                    If you wrap a ZN() around the MIN(0) then the ZN() will be computed after densification and that additional mark will be displayed.

                                     

                                    This is a really old thread and in earlier versions of Tableau if we didn’t have data preparation capabilities we had to do more densification in Tableau, and as you can see from this question the densification depends on understanding order of operations and other aspects of Tableau. Nowadays with cross-database joins, join calculations, Level of Detail Expressions, and Tableau Prep we’ve got a lot more ability to pad out the data within the data source (vs. densification that is in the view) I find those work a lot more reliably than depending on densification features.

                                     

                                    Jonathan