9 Replies Latest reply on Sep 26, 2014 12:05 AM by Francesco Rinaldi

    Can I add rows to underlying data from Tableau?

    Francesco Rinaldi

      Hi all,

       

      I have the following data:

       

      LocationDateValue
      Sydney201113
      Sydney201254
      Sydney201343
      New York201132
      New York201212
      New York201343
      London201132
      London201223
      Chicago201121

       

      and I want to get a count of each location per date, including missing cases (as per image below).

       

      Capture_1.PNG

       

      No problems so far, using a new calculated field LOOKUP = ZN(LOOKUP(COUNT([Value], 0)) I was able to achieve this:

       

      Capture_3.PNG

       

      But if I want to look at it "the other way" (eg: putting "Dates" next to "Location" in the Rows shelf) I get the following, which is not what I want. I would still like to see the cases with 0-counts (eg: London 2013, Chicago 2012 and Chicago 2013):

       

      Capture_4.PNG

       

      I tried to play around with various functions and missing values options (although those are not missing values, the entire case is actually missing).

       

      My understanding is that Tableau is not able to create new cases/rows without acting on the underlying data (adding rows, merging, blending, etc...).

       

      Is this the case or am I missing something and there is a work around?

       

      Thanks,

      Francesco

        • 1. Re: Can I add rows to underlying data from Tableau?
          Jim Wahl

          Tableau has some interesting (i.e., mysterious) ways of adding rows through a process called densification, domain completion or domain padding. I'm not sure why densification is triggered when the Date is on the Columns shelf and not on the Rows shelf, but ...

           

          The easiest way to trigger densification is to use a proper date field. I'm guessing from your data table that Date is an integer. Change this to a date using a function DATEPARSE('yyy', STR(Date)).

           

          Now when you add this to the rows shelf you should see the full set of 12 marks (4 locations * 3 years) --- as long as you have the table calc addressing / compute using set to the new Date field.

          1 of 1 people found this helpful
          • 2. Re: Can I add rows to underlying data from Tableau?
            Jonathan Drummey

            Hi Jim,

             

            There are several kinds of densification in Tableau, here's a list from memory:

             

            crosstab domain completion - this occurs when there are discrete dimensions on opposing Shelves (Rows, Columns, Pages), sparse data (not all combinations of those dimensions exist), and a table calc with the right Compute Using (which is most of them). This is what was seen in the first case.

             

            date domain completion - Tableau automatically completes the domain for dates when there's a table calc with the right compute using

             

            line and area mark type domain completion - An automatic domain completion whenever line & area marks are used, this is what enables the Format->Special Values options.

             

            Show Empty/Rows Columns domain completion - There are some special cases where Show Empty Rows and/or Columns would trigger domain completion, this might have been turned off in 8.2.

             

            At the Level domain completion - There's a special case when a dimension used for At the Level is put on the Level of Detail Shelf where domain completion can be triggered.

             

            domain padding aka Show Missing Values - This is a different kind of padding where Tableau will automatically fill out the missing values for a "range aware" dimension pill - a date, datetime, or Bin.

             

            Part of the "fun" of densification is that multiple forms of densification can be in play at once, with similar effects.

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: Can I add rows to underlying data from Tableau?
              kettan

              Tagging this comment so I can quickly find it when needed ... which is soon since I have decided to focus on learning as much as I can about table calculations as my next step in learning Tableau.

               

              Tags:   pt_table_calculation    pt_densification

              • 4. Re: Can I add rows to underlying data from Tableau?
                Jim Wahl

                Thanks Jonathan -- I was  hoping you'd chime in on this question.

                 

                Can we start a petition for official Tableau technical documentation?

                • 5. Re: Can I add rows to underlying data from Tableau?
                  kettan

                  I hope Tableau hires Jonathan & Joe for this task, so they together with Tableau's technical writers can make a documentation which is as easy to understand as it is possible. Of course only if they are willing and have the time to take such a task on themselves.

                  • 6. Re: Can I add rows to underlying data from Tableau?
                    Jonathan Drummey

                    @Jim - I asked the devs who are working on densification about that, they

                    are planning on making a number of changes (in a good way) for 9.0 and they

                    were wondering whether they should try to push out something that documents

                    what is (and is highly complicated) or put their resources into what's next?

                     

                    @kettan - It's part of the book Joe and I are writing, though to get there

                    with densification we have to understand a number of other factors first,

                    and writing all of that has taken awhile.

                     

                    Jonathan

                     

                     

                    On Thu, Sep 25, 2014 at 1:31 PM, Jim Wahl <

                    • 7. Re: Can I add rows to underlying data from Tableau?
                      kettan

                      Thanks. I look forward to read the book.

                       

                      t_study t_table_calculation       search tags & kettan

                      • 8. Re: Can I add rows to underlying data from Tableau?
                        Joe Mako

                        It sounds like you are looking for what Jonathan mentioned as:

                        date domain completion - Tableau automatically completes the domain for dates when there's a table calc with the right compute using

                         

                        Check out this segment of a video, starting at 5:20 (the link should jump to the point) http://jimw.me/1ptIhu4

                         

                        In this video, densification is on when it was wanted it off, and you want the reverse.

                         

                        So you can get densification by turning your Date field into an actual date field, and then using the Date field as the addressing/Compute Using, as in the image below. I used your same formula for Lookup (although you were missing a parenthesis), and added a formula to convert the year number to a date: DATE(STR([Date])+'-01-01')

                         

                        date data type.png

                        • 9. Re: Can I add rows to underlying data from Tableau?
                          Francesco Rinaldi

                          Thanks to everyone!! Really helpful and insightful comments!

                           

                          And thanks for the example Joe, much appreciated! (btw the parenthesis one was a copy/paste error),

                           

                          Francesco