11 Replies Latest reply on Jan 25, 2017 7:47 AM by Stuart Cary

    Show axis labels with Null values

    Hoof Hearted



      I have a chart where the x-axis labels for each month are showing, except November as that month has no data. How can I show the "November" label on the axis without there being any associated data?




        • 1. Re: Show axis labels with Null values

          Hi Hoof,


          Were you able to come up with a solution for this.

          I have a similiar problem but unable to find anything on the forums.


          Thanks for your time,


          • 2. Re: Show axis labels with Null values
            Shawn Wallwork

            Have you tried ISNULL() or IFNULL() functions?



            • 3. Re: Show axis labels with Null values
              Shawn Wallwork

              Thinking about this more, I think what you guys are really looking for is "Show Missing Values". Click the pill and see if it's there. If it's not there, then you probably have "Exact Date" checked. Change to some other aggregation DAY([Date]) for instance, and it should become available. Let me know if you have any problems getting it to show.



              • 4. Re: Show axis labels with Null values
                Jonathan Drummey

                @Shane - a clarification: The only times I know of that you won't see Show Missing Values for a date pill are:

                • Using Exact Date for a DateTime - Tableau probably doesn't make it available because it could easily lead to excessive padding and out of memory errors, you can get the same results by setting the pill's aggregation to the Datetrunc version of SECOND(). Tableau defaults to including most dates as datetimes (like Order Date and Ship Date in the Superstore Sales), that's why you don't see the option there.
                • When the date pill is on the Marks Card or Pages Shelf. In this case, even though Show Missing Values is not an option, the date pill will preserve whatever setting of Show Missing Values existed. (However, in that case in order to retain the padding created by Show Missing Values there needs to be a table calc with a compute using that includes the date pill).



                • 5. Re: Show axis labels with Null values
                  Richard Leeke

                  Just expanding slightly on the comment about why show missing values isn't available for datetime - it is just that datetime fields are continuous and can hold values down to whatever level of accuracy the underlying database supports (even if Tableau stubbornly refuses to let you display that accuracy).  Typically I think that is something like 10 microseconds -so you would need to fill in 100,000 marks between each pair of seconds.  The reason for that out of memory comment becomes clear.

                  • 6. Re: Show axis labels with Null values
                    Geoff Ball


                    I have a similar issue.  The 'show missing values' works well when you have a hole in data (e.g. have data for Mar and Sept, you can populate blanks between them i.e. Apr-Aug.  I want to display null (then turn it to a zero) for months outside the data range i.e. for Jan-Feb and Oct-Dec.


                    Is there any way to do this?  I'm cutting incident data over the last 5 years by town - and trending the frequency and then calculating a moving average.  I have data up to Jul '13 - but some towns have few incidents and their last one was say in Feb '13.  Currently the chart stops in Feb, when actually no data in Mar-Jun means zero in Mar-Jun, and the moving average should continue (and decrease).


                    Hope the above makes sense.  And hope someone can help.

                    • 7. Re: Show axis labels with Null values
                      Shawn Wallwork

                      Geoff, without seeing a workbook I'm only guessing, but it sounds like you might be able to get what you want using two "tricks":

                      1. Wrap your calcs in ZN(), this will turn nulls into zeros
                      2. Use a reference line to trick Tableau into displaying all the months you want.


                      If these don't work for you. Post a packaged workbook and I'll be happy to take a look.





                      • 8. Re: Show axis labels with Null values
                        Geoff Ball

                        Hi Shawn,

                        Thanks for the reply.  I have used trick 1 (this is what populated the holes in the data = Apr-Aug) - but it doesn't seem to populate nulls/zeros outside the data range.  Can you expand on what you mean by reference line trick?

                        I have forced the axis of the chart to display the range I want (Jan-Dec '13), but I still just get blanks (i.e. not null or zero) at either end.  When you click 'display data' the 1st row is Mar '13 and the last Sep '13 (i.e. no nulls/zeros for the Jan/Feb and Oct-Dec I wanted).


                        One issue I noticed when I last exported a packaged workbook - the dates in the data went all screwy (data from 2004-2013 changed to 1970-2035!)  Does anyone have experience of this

                        • 9. Re: Show axis labels with Null values
                          Shawn Wallwork

                          Geoff, to your first questions. The reference line 'trick' I was referring to isn't appropriate here, as you are fixing your axis so it won't have any affect. Tableau will do data densification and show your zeros between MIN([Date]) and MAX([Date]), but as you've discovered T will not add data outside this range. There's probably a way to add this data using a calculated field, but we're really going to need to see a sanitized workbook to help you much more, especially as your dates are going "screwy".





                          • 10. Re: Re: Show axis labels with Null values
                            Geoff Ball

                            Hi Shawn,

                            Thanks for your help/interest.  Sorry for the delay in my reply.  Attached is a portable workbook with tiny data that hopefully illustrates my problem.  It has two catchments, A and B, with incidents in each one.  A has one incident per month, B has a gap in incidents, and also has no incidents towards the end of the year.

                            I want to view a bar chart on the number of incidents per month, and I want to have a moving average also.

                            Currently A works OK (though a flat line isn't the best way of demonstrating this).  B doesn't work, and hence neither does A+B.


                            I think that because B has nulls, they are not counted as zeros and so don't bring the moving average down.  I have tried to use IFNULL and ZN but couldn't get either to work...


                            The second issue is that B has nulls towards the end of the year, I have fixed the x axis (as I want to see the same yearly period for each catchment), but because of the nulls, the moving average line is not populated beyond the last non-null value.


                            Hopefully this explains things a little.



                            • 11. Re: Show axis labels with Null values
                              Stuart Cary

                              I know this question was asked a long time ago but it shows up as one of the top hits in Google so wanted to share a potential solution that might help someone. In order to show the months that were outside of the data range (in my case Jan-Mar, Nov-Dec), I first right clicked on the month pill and selected "Show Missing Values" as others mentioned and then I also had to go to Analysis->Table Layout->Show Empty Columns. Not sure if this is a new feature in Tableau 10 or not.



                              In my case I was trying to show a "Actual vs. Goal" chart and didn't need to calculate a moving average like Geoff which seems much more difficult. Luckily my goal is the same every month. So I have a calculated field equal to my goal and I added that to the detail of the Viz. Then I created a reference line and when you set it to "Entire Table" or "Per Pane" it goes across the whole chart, including the months with no data.