1 2 Previous Next 23 Replies Latest reply on Nov 4, 2017 8:00 AM by Berton Harrah

    Displaying 0 in null fields

      Hi, I'm hoping someone may be able to help out. I posted on an old discussion but haven't had any responses, so hopefully starting a new one may get a nibble. This was the previous discussion: http://community.tableau.com/message/172537#172537

       

      If anyone can assist, it would be greatly appreaciated.

       

      Thanks in advance,

      Erin

        • 1. Re: Displaying 0 in null fields
          Tracy Rodgers

          Hi Erin,

           

          That post seems to have been removed. Maybe you can re-post your question?

           

          However, based no your title of the post, there are a couple of possible options. A calculated field can be created that uses the ifnull function. The formula would look similar to the following:

           

          ifnull([Profit], 0)

           

          Another possibility is to right click on the measure (in the view) and select Format. Make sure the Pane tab is selected. Under Special Values type in 0 in the text box.

           

          Hope this helps!

           

          -Tracy

          2 of 2 people found this helpful
          • 2. Re: Displaying 0 in null fields

            Hi Tracy,

             

            Thanks for your response, I have tried both of those solutions but neither have worked for me. This was my original post:

             

            "Hi James,

            I'm a bit stuck and hope you can help. I've tried your formula

                            IIF(ISNULL([data]), 0, [data])

            but it's not worked. I've also tried

                            IIF(ISNULL([data]), 0, 1)

            and still not luck. Would you be able to have a look at the attached package and see where I'm going wrong? I've searched through the forum for a solution but haven't been able to find anything that works.

            Thanks,

            Erin"

            Greatly appreciate any help. I've attached the package workbook as well for reference.

            Thanks again,

            Erin

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

              The basic issue is that there doesn't seem to be any data for those combinations of dimensions you are using, and Tableau doesn't make up data when there isn't any. "No data" is different from Null, and that's why the ISNULL() function is failing.

               

              However, we can tell Tableau to generate a null when there isn't any data, and that's using the following table calculation: LOOKUP(SUM([Flag]),0).

               

              This table calc returns SUM([Flag]) for the current row, and Null when no value is found. Then you can wrap that in the IIF(ISNULL()) operation to return 0 or a value. alternatively, you could just do the lookup and then Format the calculation to set 0 to be the Text for Special Values.

               

              Workbook is attached.

               

              Jonathan

              6 of 6 people found this helpful
              • 4. Re: Displaying 0 in null fields
                Jordan Carson

                I have just tried following the steps provided by Jonathan, but am still unable to get this to work. Can this work in any instance? As I am not building a 'data sheet' per say but on my sheet I only need 1 number, however when there is no data tableau leaves it blank. Will this work in any circumstance? I will provide an example if needed.

                • 5. Re: Displaying 0 in null fields
                  Ranadheer Kovvuri

                  Hello Jordan,

                   

                  Can you please let me know if you find any solution for this.I have a similar problem,where i have to display a single number.If there is no data in the field,no data appears in the sheet too.I need to replace empty field with 0.Thanks

                   

                   

                  Rana

                  • 6. Re: Displaying 0 in null fields
                    Tableau kumar

                    if  is null then 0 end

                     

                    On Mon, Mar 2, 2015 at 9:47 PM, Ranadheer Kovvuri <

                    • 7. Re: Displaying 0 in null fields
                      Ranadheer Kovvuri

                      Thanks for the reply, it did not help me.It doesn't show anything.

                      • 8. Re: Displaying 0 in null fields
                        Tableau kumar

                        Hi,

                         

                        Post a new thread with clear explanation,

                         

                        Another solution to replacing nulls with o is

                         

                        iif ( isnull([Sales]), 0,[Sales] )

                         

                        Refer the following Document to get detailed information.

                         

                        Nulls Handling - String, Date, Number Data types

                         

                        Best Regards

                        Laxman Kumar

                         

                         

                        On Mon, Mar 2, 2015 at 10:00 PM, Ranadheer Kovvuri <

                        2 of 2 people found this helpful
                        • 9. Re: Displaying 0 in null fields
                          Rishabh Dhingra

                          Hi Jonathan Drummey

                           

                          I am facing the same issue. I don't have data after April and I need to show running sum data for fees. I am trying to convert Null to 0 so that I can create running sum value for the months after April as well.

                           

                          However, I am unable to convert Null to 0 and there is no data shown after April month.

                           

                          Could you please suggest?

                           

                          Thanks,

                          Rishabh

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

                            Please post a Tableau packaged workbook with some sample data. There are too many variables in what you describe for me to try to guess what your data looks like, what's in the view, or your desired results.

                             

                            Jonathan

                            • 11. Re: Displaying 0 in null fields
                              Lekshmi Santhosh

                              the zn() function replaces any null value with a zero

                              • 12. Re: Displaying 0 in null fields
                                Namrata Sawant

                                Hello,

                                 

                                Thanks for the workaround of first converting NoData to Null and then evaluating the Nulls. For some reason, this does not seem to work on a worksheet which is completely blank (no table grid with missing values) and all I need to show is a ZERO if the Expression is evaluated as NULL.

                                 

                                I have attached the packaged workbook herewith. Could you please bounce off any ideas you might have to show a ZERO on this worksheet.

                                 

                                Thanks and appreciate your help and time into this!!

                                 

                                Regards,

                                Namrata

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

                                  Hi Namrata,

                                   

                                  The Sub Region filter is on Alaska and Alaska doesn't exist in the data, so the query that Tableau is issuing to the data source is returning 0 rows. So Tableau isn't even drawing a mark, see the marks count in the bottom:

                                   

                                   

                                   

                                  So there isn't a Null-valued mark that can be turned into 0 via any type of calculation. A number of the techniques that are talked about with no data (in this thread, and links to others) take advantage of Tableau's built-in data densification behaviors, but all of those require there to be at least *some* data to pad with.

                                   

                                  Given this particular situation (the query isn't returning anything at all) the one technique that I know of is to:

                                   

                                  a) Set up the worksheet so instead of showing a blank space it shows nothing at all when there is no data. We can do this by adding a discrete pill to the view and turning off Show Header, this is the "Real data sheet" in the attached workbook:

                                   

                                  b) Create a worksheet that has the same layout that *does* show a 0. This is the "No data sheet" in the attached workbook:

                                   

                                   

                                  c) Create a dashboard and place the worksheet from a) as a floating worksheet on top of the worksheet from b), then fiddle with the alignment. Here's the dashboard filtered for Alaska:

                                   

                                   

                                   

                                   

                                  And now I've gotten rid of Alaska so the COUNTD is returning 3403:

                                   

                                   

                                  v9.0 workbook is attached.

                                   

                                  Jonathan

                                  1 of 1 people found this helpful
                                  • 14. Re: Displaying 0 in null fields
                                    Namrata Sawant

                                    Hi Jonathan,

                                     

                                    Thank you so much for your detailed response! Yes, I agree with you that since there is no mark at all, there is no calculation that could convert NOTHING to ZERO. This workaround is very helpful. Thanks a ton!!

                                     

                                    Just to give you background, I am working on an operational dashboard which needs to show the count of exceptions against different scenarios. This dashboard already has so many worksheets since each of those scenarios has a separate data source and ultimately a different database view in the backend. I wish it was possible to reuse the Zero worksheet at multiple places on the same dashboard. Or may be I will just create text boxes for the static Zero but if only we had an option to just copy paste and reuse the same text box already created.

                                     

                                    Again, greatly appreciate your time and effort into this!. Have a good one!

                                     

                                    Best Regards,

                                    Namrata

                                    1 2 Previous Next