6 Replies Latest reply on Aug 15, 2013 12:09 PM by Mark Holtz

    Table Calculation Mystery

    chris.farr

      I truly must be missing something. Any help much appreciated. Consider the following data (also attached in the Excel document):

      data.jpg

      I load this into Tableau. I'm able to do a running total left-to-right when doing a text-based table:

      table.jpg

      Yet, when I do a stacked bar chart (by dropping Product on to the Color shelf), I get an odd result where the stacked bars aren't always increasing left-to-right, which is what you'd expect:

      bar.jpg

      Even more puzzling is that if you export the Crosstab to Excel, the underlying data table behind the chart is what you'd expect:

      excel.jpg

      (Note how the Excel table aligns exactly to the Tableau table above.)


      So, what is going on here? Why can't Tableau simply visualize the underlying data in its Crosstab? Or, how can I get my stacked bar chart to look as expected, namely like this (done in Excel):

      chart excel.jpg


      Thanks in advance.




        • 1. Re: Table Calculation Mystery
          Pedro Machado

          Chris,

           

          I am curious about this as well. If you use an area chart, you get the expected result.

           

          Could someone explain why the bar chart behaves differently?

           

          Thanks,

           

          Pedro

          fruits.jpg

          1 of 1 people found this helpful
          • 2. Re: Table Calculation Mystery
            chris.farr

            Pedro,

             

            Thanks much for looking into this! Great to know the Area chart works.

             

            I'm hopeful somebody out there has insight into why stacked bar chart does not work out.

             

            Could this be a bug even?

             

            Chris

            • 3. Re: Table Calculation Mystery
              Aaron Clancy

              Visually, Tableau needs a place to put the the data, so for example there are no bananas in C so it skips it until it sees another intersection of product and category.  To get around this I use blending with a table that has the all possible intersections (quick fix) if you need it to be more dynamic you'll need to leverage a sql join.  After blending I created a calc that converts the nulls for "value" to "zeros"    zn(min([Value])) .... This creates the intersection that Tableau needs in order to show the data in a spot were no data previously existed.(The reference table I created needs to be the primary source)

              Screen Shot 2013-08-14 at 9.23.00 AM.png

              In regards to why area charts appear to work:  Visually, Bar charts don't care what's to the left or right of a specific bar but area charts do.

              The reason an area chart works is because the concept "Show at Default" can be applied when formatting the Value field.  This tells the mark to continue on even when data isn't there essentially just connecting the dots.

              Screen Shot 2013-08-14 at 9.30.05 AM.png

              1 of 1 people found this helpful
              • 4. Re: Re: Table Calculation Mystery
                chris.farr

                Aaron,

                 

                Thanks so much for your thoughtful reply. Here's a wrinkle. Curious to your thoughts.

                 

                Imagine I added a column to the data named Date. This file is attached.

                data2.jpg

                 

                Note how Category A = 1/1/2013, Category B = 2/1/2013 and on and on.

                 

                Tableau recognizes this new column as a date data type. If I do a stacked bar chart where for Date I do an Exact Date and Discrete,

                date settings.jpg

                 

                I get my desired result -- see below. Any thoughts on why putting Date (w/ options noted above) in Columns shelf works and Category does not?

                bar with date.jpg

                • 5. Re: Re: Table Calculation Mystery
                  Aaron Clancy

                  Dates have a sense of progression and relation to each other, much like what is described as the behavior in an area chart, while the distinct category values do not so I assume Tableau is designed with that in mind.  Maybe someone else can confirm that.  I might be stabbing in the dark with that :-)

                  • 6. Re: Table Calculation Mystery
                    Mark Holtz

                    I believe Aaron is onto it. A few random thoughts:

                     

                    Since dates are actually a special case of a discrete dimension, when dealing with discrete dates (date pill is blue), Tableau can essentially "connect the dots" as Aaron put it, due to its innate ability to detect "missing" values in a date sequence. By definition, discrete means that the categories are wholly independent and you would not necessarily expect any logical/ordinal progression. So, when you are dealing with discrete text fields (A, B, C, E) Tableau cannot (and probably should not) attempt to fill in "missing" values. And just because Tableau sorts these example discrete members alphabetically does not mean it presumes to fill in a "D" where there is none.

                     

                    Just to be thorough here, Tableau can treat discrete dates in a couple ways. If there is at least one value intersecting each date, it doesn't have to think about it and will show all the dates. But if there were at least one date that was missing from the date sequence, the situation changes slightly. You tell Tableau whether or not you want it to show dates that are entirely missing by right clicking on the discrete date axis and toggling on the "Show Missing Values" option.

                     

                    A simple example of this is to think of an hourly employee's daily time card being graphed. They could show ~8 hours Mondays through Fridays, but 0 on weekends. So if you had a bar graph, you would either exclude dates that are  Saturdays and Sundays, or include them and introduce gaps in your "bar" visualization.

                     

                    In a this example case though, there is a value for each day, so that Show Missing Values issue is not what's at play. Here, I think you are benefiting from the Special Values handling.

                     

                    I believe the default in Tableau is to show nulls as 0, which allows calcs like running sum to continue, even over a gap that can be "filled in/over"--meaning mostly just dates--as again Tableau has the ability the "fill over" missing dates, but not "missing" discrete text categories.

                     

                    That seemed a little clearer in my head, so hopefully it is helpful.