1 2 3 4 Previous Next 57 Replies Latest reply on Jul 26, 2018 10:00 AM by Joe Mako Go to original post
      • 45. Re: Force Blanks to Show.
        Simon Runc

        Awesome!

         

        So yes there is a difference, but in your case it isn't going to help.

         

        Excluding NULLS (or any set of values) means those rows are filtered from the data (so say you had a YoY calculation based on 2011 vs 2012, if you excluded 2011; the 2012 variance could no longer calculate, as it don't have access to the previous years numbers)

         

        Hiding, removes those marks from the Viz, but leaved those rows in the data. So in the above example, we'd only see 2012...but the variance calculation could still be performed, as it would have access to the 2011 numbers. When we hide, and so remove the marks from view, the axis does get re-drawn to "what's in view" and not "what's in the data"...as the below gif shows

         

        Hiding.gif

        1 of 1 people found this helpful
        • 46. Re: Force Blanks to Show.
          Lucie Brett

          Hello Simon,

           

          Hope you are well!

           

          I am hoping to build a dashboard which shows a small line graph for 40 odd different dimensions; this should look like sparklines as I want to just compare the general trend for each dimension over 4 years.

           

          The reason I wanted to add to this post is that I love your LoD to detail solution but what if I don't have data for some of the years?

           

          For that line graph, the first year(s) won't show. Is there a way to ensure NULL appears in this scenario?

           

          I could try and build a mock dataset to show what I mean but I just wondered if this had an easy answer.

           

          Thanks in advance, Lucie

          • 47. Re: Force Blanks to Show.
            Simon Runc

            hi Lucie,

             

            All good here, thanks...Hope all is good with you.

             

            So Yes I think you can use the same trick here...In the attached I've dummied up some data where we only have discount from 2016 and sales from 2015. I've created a [Min Year - LoD] calc...

            {MIN([Year])}

             

            and then used this to create a reference line in both tabs, to ensure both start at the same place...we could also do something similar for {MAX([Year])} to ensure the same Axis Range.

             

            Hope that's what you mean, but let me know if not.

             

            btw, I've let the reference lines visible so you can see what's going on, but you can set the line  to 'none' for your final version, so the end user won't see it.

            2 of 2 people found this helpful
            • 48. Re: Force Blanks to Show.
              Lucie Brett

              Thanks Simon, this is a great solution. 

               

              Had to tweak a few things i.e. make the year a number and continuous etc but otherwise an elegant solution as usual!

               

              Have a great day!

              • 49. Re: Force Blanks to Show.
                suvas.chandra

                Joe,

                 

                Would you please shed some light on this example:

                 

                Showing empty column

                 

                 

                I would really appreciate

                • 50. Re: Force Blanks to Show.
                  suvas.chandra

                  Joe,

                   

                  How will you combine Years to this ? For me, the months formula you gave works good except it is returning blank. And how will you combine months and years into one formula.

                   

                  This was my original formula:

                   

                  If ISNULL([Order Date]) Then 'No Plan'

                  ELSEIF DATEPART('year', [Order Date]) < 2011 Then '<2011'

                  ELSEIF DATEPART('year', [Order Date]) = 2011 Then '2011'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'January' Then 'January'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'February' Then 'February'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'March' Then 'March'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'April' Then 'April'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'May' Then 'May'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'June' Then 'June'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'July' Then 'July'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'August' Then 'August'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'September' Then 'September'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'October' Then 'October'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'November' Then 'November'

                  ELSEIF DATEPART('year', [Order Date]) = 2012 AND DATENAME('month', [Order Date]) = 'December' Then 'December'

                  ELSEIF DATEPART('year', [Order Date]) = 2013 Then '2013'

                  ELSEIF DATEPART('year', [Order Date]) = 2014 Then '2014'

                  ELSE '2014>'

                  END

                   

                   

                  I can get the Months if I use your formula. But I cannot combine them into one formula.

                   

                  I would really appreciate your help.

                   

                  Thanks

                  • 51. Re: Force Blanks to Show.
                    Giorge Luiz

                    I have a similar problem.

                     

                    I'm trying to show the number of registers in my dataset by category and ***. The problem is that some of the categories doesn't have a single row of a certain ***, but this data still relevant for my client, so I want this data to be displayed as zero.

                     

                    Capture.jpg

                     

                    You can see here that the category 'J1234' for example doesn't show the 'M' data, cause it doesn't exist in the data source. How can I display this info?

                    • 52. Re: Force Blanks to Show.
                      Lucie Brett

                      Hi George,

                       

                      Could you move the M/F dimension to the columns shelf?

                       

                      Lucie

                      • 53. Re: Force Blanks to Show.
                        George Lapinlampi

                        Fantastic! Thanks a lot Joe!

                        • 54. Re: Force Blanks to Show.
                          dinesh punnam

                          Hello All,

                           

                          I have a filter called country and if i select USA it should give me only United states values and rest zero

                           

                          - like below ( achieved using parameter) but dint wan to use use parameters as country column will be context filter.

                           

                           

                           

                          what's happening is if i select as country the other country in the view are going off - do we have anything in tableau  to show all countries but should show zero ,like below

                           

                           

                          Thanks

                          Dinesh

                          • 55. Re: Force Blanks to Show.
                            Richard Bout

                            Thank you, this solution worked great for me.

                            • 56. Re: Force Blanks to Show.
                              Brendan van Rijn

                              Hi Joe Mako,

                               

                              Thanks for sharing this, it really helped! Next step for me is to get this to work in a map as well. I've tried several suggestions in this thread as well as others, but so far seem to work when applied to maps.

                               

                              Using the workbook you've edited earlier, I tried to convert that into a map but it loses the densification from the lookup calculation. The Technology/Jumbo Box value is still missing. Using a ZN or ISNULL functions do work when looking at a cross table but not in a map.

                               

                              Any ideas on how to tackle that?

                              • 57. Re: Force Blanks to Show.
                                Joe Mako

                                For that situation, I would recommend a scaffold, a data source that contains every possible dimension combination of interest, used as the primary, and then blend in your measures to be displayed.

                                 

                                Attached is an example.

                                1 of 1 people found this helpful
                                1 2 3 4 Previous Next