1 2 Previous Next 15 Replies Latest reply on May 7, 2012 12:09 PM by jwode

    Show all, even if NULL?

      I'm stumped.  I want to display a continuous line chart.  But where there's no data to be aggregated that week is skipped.  If you look at the attached image you'll see that weeks 3, 4, 6, and 9 are missing even though they are part of the selected range in the filtering (range is weeks 1 - 12).  I have a date dimension and it's used as a filter by Year and Month.  The week part of the date is a "week number" (x-axis on attached chart).  The attached is a Line (Discrete) chart; Line (Continuous) is broken (bug has been submitted) so I don't know if this a possible fix or not.

       

      Judging by the chart, zeros are coming thru (orange line) so I'm guessing that if there are no values (NULL) for that week then they aren't even presented, thus the "missing" week numbers.  Is there something obvious I'm missing to untoggle this so I get all weeks no matter what?

        • 1. Re: Show all, even if NULL?
          Joe Mako

          Generally if you want Tableau to draw a mark at a point, you have to have that combination of dimensions that will be that point in your data source. Tableau will not pad your data source, so if you want it padded with null values, you will have to do that before you connect Tableau to your data.

           

          There are a few other options to help you pad your data with null values:

          - Use a data blend, with the primary data source one that has every possible combination of dimensions that will make a mark, then if there is a value missing in your data source, a null value will be placed.

          - Use Custom SQL, varies by situation, could be a UNION, and/or a CROSS JOIN to generate the combinations of data that you want.

          - In some cases, you can use a table calc to pad your data when using a crosstab layout.

           

          Can you mock up what you want your end result chart to look like, and provide a sample data set or workbook?

          • 2. Re: Show all, even if NULL?

            Joe, last night at home I was thinking about my issue and realized -- duh! -- that after I had looked at the underlying data that there was no data for those missing points!  So you're suggestion of padding the data manually is spot on to what I'm thinking.

             

            I figured out something yesterday in Tableau that I had thought was not possible so I'm going to attack it this problem again now that I know what my issue is.

            ~Tableau Padawan  ;-)

            • 3. Re: Show all, even if NULL?

              I have the same issue--I want to show data across a continuous time period--but I don't have any actual numbers in my dataset. I'm using "count distinct" to display how many unique workshops were offered each year. One year has no workshops, but I want it to show up on the graph as zero. Tableau is just eliminating the year from the graph. Suggestions?

              • 4. Re: Show all, even if NULL?
                Joe Mako

                jwode, can you provide a sample packaged workbook that demonstrates your situation?

                • 6. Re: Show all, even if NULL?

                  I've attached a sample packaged workbook. When all workshops are counted, you can see every year from 2005 to 2009. When only Photography workshops are counted, however, 2007 disappears because there were no Photo workshops that year. I don't see a way to make 2007 show up on the graph with a count of 0. Thanks for any help you can provide.

                  • 7. Re: Show all, even if NULL?
                    Richard Leeke

                    There have been numerous threads discussing this over the years.

                     

                    Have a read of this thread - I posted an example here to try to explain what was happening and Joe followed up with a modified version demonstrating one approach to filling in the gaps.

                    • 8. Re: Show all, even if NULL?

                      Joe says this approach "does require an understanding of SQL" which unfortunately I do not yet have, though it is a goal for the future. I'm disappointed something this basic doesn't seem to be built in. It seems that, for a time-series graph, users should be able to right-click the axis and select, "Show all months [weeks, etc.] in between start and end dates."

                      • 9. Re: Show all, even if NULL?
                        Joe Mako

                        You could also approach the situation from another angle, with a parameter and a calculated filed like:

                         

                         

                        IIF([Department]=[Department Parameter] OR [Department Parameter]="All",[Number of Records],0)


                         

                        This is done in the attached.

                        • 10. Re: Show all, even if NULL?
                          Chris Keenan

                          Hope it's not too late for me to join the party. Maybe Richard and Joe are looking for some nostalgia?

                           

                          I'm having the same problem. I have a line chart where it displays the number of total logins (login_count.png) and number of unique logins (weekly_unique_id.png) per week. In case it's not clear, I determine the weekly unique logins by combining the UserName and Week Ending field and then do Count Distinct.

                           

                          As you can see, I'm having a problem with gaps in the data (sheet_view.png). I know I need to pad the data. I'm just not sure how to go about doing it. Unfortunately, I can't attach my workbook due to client confidentiality (it's also QUITE large). But if there's a specific request in terms of data structure, I'll do whatever I can to answer it or sanitize the data to make it easier to help me out.

                           

                          Thanks in advance for the help!

                          -Chris

                          • 11. Re: Show all, even if NULL?
                            Joe Mako

                            You can wrap your aggregations inside of the ZN and LOOKUP functions like:

                             

                             

                            ZN(LOOKUP(SUM([Login Count]),0))


                             

                            and that will add zeros because of your crosstab layout.

                            • 12. Re: Show all, even if NULL?
                              Ross Bunker

                              Toby, you should try this in Tableau 7.0.  Does it do the right thing now?

                              thanks,

                              :)ross

                               

                              Ross Bunker

                              Senior Software Engineer

                              Tableau Software

                              • 13. Re: Show all, even if NULL?

                                Hi Ross,

                                I have Tableau 7.0. How can I show null values in my graphs? I have an area graph that looks rather silly (see image) because two of the five cohorts didn't have anyone who said "Somewhat important" (green), and only the last cohort said "Only a little important" (orange). I would like to fill in the area even if it disappears at the exact point there are no responses.

                                 

                                Picture 1.png

                                • 14. Re: Show all, even if NULL?
                                  Ross Bunker

                                  Hi . Jwode,

                                  I think you just need to tell Tableau to display nulls at the 'Default Value' (in this case, 0).  See:

                                  http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/formatting_specific_nullvalues.html

                                   

                                  Let me know if that doesn't work.

                                  1 2 Previous Next