14 Replies Latest reply on Oct 12, 2016 1:45 PM by Matt Penner

    Filling in Zeros

    Matt Penner

      I have seen a lot of answers in regards to this but none seem to fill my need.  Below is a chart I have made and as you can see there are a lot of blanks.  I would like to fill them in with zeros but that is only my first problem.  As you can see by the 2nd screen when I do a 3mm move it completely drops Geico since there are no numbers for the last 3 months.  Is there a way to show Geico at 0% on the 3mm view?

       

       

       

       

       

      here is the 3mm view.  you can see that Geico is not listed

        • 1. Re: Filling in Zeros
          Lisa Li

          Hey Matt,

           

          Have you tried ZN(CNT(Policy Number)) ?

          This will return count of policy number when not null, and 0 when null.

           

          -Lisa

          CoEnterprise | Home

          • 2. Re: Filling in Zeros
            Matt Penner

            Lisa,

            Am I missing something?

             

             

            Matt Penner

            • 3. Re: Filling in Zeros
              Joshua Milligan

              Matt,

               

              From the top menu, select Analysis > Table Layout > Show Empty Rows.  This will give you a row for all values of Prior Insurance Group that are present in the data (or context, if you add a filter to context).  However, all values in the pane will show up as blank (in fact, there will be no mark at all) because the data literally does not exist (it's not even NULL -- it's just not there).

               

              Edit: This is specifically for the 3mm table.

               

              Hope that helps!

              Joshua

              • 4. Re: Filling in Zeros
                Vincent Baumel

                Here's a solution to problem 1. If you create a calculated field that looks like this:

                 

                LOOKUP(COUNT([Policy Number]),0) and use that in place of [Policy Number] on your marks card then it will perform a table calculation that gives you either your target data or null. It will look the same at first, but don't worry! We can right click on the data in the viz and hit Format. From the Fields dropdown, select CNT(Policy Number) and take a look at the Special Values section down at the bottom on the left pane of the screen. In the "Text:" box enter 0% so that those null values are represented just the way you want them.

                 

                 

                If that helps mark this as correct and let me know!  Good luck!

                • 5. Re: Filling in Zeros
                  Shriram Saxena

                  Hi Matt,

                   

                  Try Using IfNULL may be it will help.

                   

                  Thanks

                  Shriram Saxena

                  • 6. Re: Filling in Zeros
                    Vincent Baumel

                    Shriram-

                     

                    This will only work if the data is actually null. There's a difference between no data and null data, and in this case the data isn't even there to begin with. That means it's technically not null, so using an IFNULL wouldn't return anything.

                    1 of 1 people found this helpful
                    • 7. Re: Filling in Zeros
                      Matt Penner

                      Vincent,

                      how do I get my %'s back for my non zero fields.  before it was a table calc but i cant do that now.

                       

                      • 8. Re: Filling in Zeros
                        Vincent Baumel

                        Right click Lookup in the Measures list on the left. Default Properties -> Number Format. Go to Number (custom) and bump the decimal places down to 0, then put a % in the Suffix box. Voila!

                        • 9. Re: Filling in Zeros
                          Vincent Baumel

                          By the way, if the data isn't matching up to what you think it should, right click Lookup in the Marks card and play around with the Edit Table Calculation dialogue box. The Compute Using may be off, but that should be where you find your expected results.

                          • 10. Re: Filling in Zeros
                            Joshua Milligan

                            Matt,

                             

                            Your getting great suggestions from every angle! And I think Lisa and Vincent are on the right track with their suggestions for problem #1.

                             

                            For #2, see my proposed solution above.  There is no way I am aware of to get a 0% in that one, but you can at least get all rows to show.

                             

                            But for #1, I would simplify the approach as follows:

                            • Use Lisa's first suggestion: ZN(CNT(Policy Number))
                            • Additionally, create a calculated field with the code Index() and add this to the Detail of Marks.

                             

                            What Index() does is enable "data densification" and the missing data gets filled in with NULLs that you can change to 0s with ZN().

                             

                            Edit: VIncent's approach also enables data densification and will work.  The complexity here is that it's not just LOOKUP(COUNT([Polity Number]), 0) it is LOOKUP(COUNT([Polity Number]), 0) / TOTAL(COUNT([Policy Number]) and you'll have to set it to compute over the whole table (either explicitly or implicitly).  I would find this a bit more complex, but it's a great thought process and works too!

                             

                            Hope that helps!

                            Joshua

                            2 of 2 people found this helpful
                            • 11. Re: Filling in Zeros
                              Matt Penner

                              Joshua,

                              Thanks that was a simple solution.  I will have to figure something else out for my second question.

                               

                              Thanks

                              • 12. Re: Filling in Zeros
                                Joshua Milligan

                                Matt,

                                 

                                Here are a couple of alternate approaches for #2:

                                • Create a data source that has includes a row for every provider for every month (and multiple rows for each potential filter field - e.g. TSL Number). You might even be able to accomplish this fairly easily using Tableau 10's cross database join.
                                • Create a simple data source that just has a provider column with every possible provider.  You can then use that as the primary data source to show rows for all providers and then blend to your existing data source on Provider and use calculations to get the count of providers from the secondary source (or zero if no match is found).

                                 

                                The difficulty with the second approach is that the filters I see in your view above are going to be problematic (because they are global to the data source if it is a primary data source and not secondary).  The first approach could work well, but will take some effort to setup and some care to ensure that you don't artificially inflate values.

                                 

                                Best Regards,

                                Joshua

                                • 13. Re: Filling in Zeros
                                  Joshua Milligan

                                  Matt,

                                   

                                  One more possibility for #2, and this one should be a "little easier".

                                   

                                  • Take your original view (with all the months) and duplicate it.
                                  • Remove CNT(Policy Number) from Text
                                  • Create a calculated field called Last with the code Last() add it to the Detail of Marks, set it to Compute Using > Month (using the drop down on the field)
                                  • Create a calculated field called 3 Month Total with the code WINDOW_SUM(IF [Last] <=2 THEN SUM(1) END)
                                    • The Last calculation will ultimately give us the last 3 months (values of 0, 1, and 2) so 3 Month Total will be the sum of the 3 months
                                  • Create a calculated field called All Provider Total with the code: WINDOW_SUM([3 Month Total])
                                    • Ultimately this will give us the total for all providers for the last 3 months
                                  • Create one final calculated field called % of Total with the code IF [Last] = 0 THEN [3 Month Total] / [All Provider Total] END
                                  • Place % of Total on Text

                                   

                                  Now the hard part

                                  • You have to get all the Nested Calculation "Compute Usings" Correct:
                                    • Use the drop down on [% of Total] and select Edit Table Calculation
                                    • In the window, step through each nested calculation and set them as following:
                                      • Last to use Specific Dimensions: Month
                                      • 3 Month Total Month to use Specific Dimensions: Month
                                      • All Provider to use Specific Dimensions: Provider

                                   

                                  (In previous versions of Tableau, just use the Compute Using drop down to select Advanced and then move the indicated dimensions over to Addressing)

                                   

                                  • using the drop down on the field Last that is on the Detail of Marks make it Discrete and then move it to Filters and select to keep 0
                                  • use the drop down on Month to uncheck Show Header

                                   

                                  You should now have a view that has all percents (and 0%) for all Providers (as long as other filters don't remove them; as long as they exist for any month - and not just the last 3 - they will show up)

                                   

                                   

                                  Personally, I think the Analysis > Table Layout > Show Empty Rows approach is a bit simpler!   But Tableau can do almost anything you want!

                                   

                                  Best Regards,

                                  Joshua

                                  • 14. Re: Filling in Zeros
                                    Matt Penner

                                    Joshua,

                                    I must have messed up somewhere because I am right back where I started.  I am still missing a row.  i will take another crack at it tomorrow.  Thanks for all your help