1 2 Previous Next 23 Replies Latest reply on May 12, 2014 5:00 AM by Sam Bruce

    Tableau 7.0 Extending Table Calculation

      With a Tableau 6.1 chart, I have a table calculation for Running Total.  For two of my customers, the data and the chart lines go from week 1 thru week 12, and for one customer the data and chart line just go from week 1 thru week 6.

       

      I also have a Target value without a table calculation that goes from week 1 thru week 6.

       

      chart 6_1.jpg

       

       

      When I view the chart in Tableau 7.0, everything remains the same as in 6.1, except that the third customer (whose data only goes from week 1 thru week 6) now has its chart line extended all the way thru week 12.  I really want this line to remain truncated at week 6, where the data ends.

       

      chart 7_0.jpg

       

      I have attached the 6.1 file here (since it can be viewed in either version of Tableau).

        • 1. Re: Tableau 7.0 Extending Table Calculation
          Alex Kerin

          This change of behavior strikes me as a bug - you don't have values for those weeks, so why are they plotted? - I would email support@tableau....

          • 2. Re: Tableau 7.0 Extending Table Calculation
            . Mar

            I am having the same issue..

            • 3. Re: Tableau 7.0 Extending Table Calculation
              Tracy Rodgers

              Hi Kenny,

               

              This is actually as designed with version 7.0. Although, I agree, there should be an option to turn this off. Perhaps make a comments under Ideas in the Community section.

               

              However, there is a workaround to get the desired result. Instead of using the quick table calculations, formulate the running_sum manually. Create a calculated field similar to the following:

               

              previous_value(sum([Customer_Qty]) + sum([Customer_Qty])

               

              Replace the table calculation that is on the view with this. Notice, the view will take a hard drop. Then, create a second calculated field using the following calculation:

               

              isnull([Calculation created above])

               

              Place this on the Level of Detail shelf, then, right click and select Filter. Check only the False box and the unwanted pieces of the line should disappear.

              • 4. Re: Tableau 7.0 Extending Table Calculation
                Shawn Wallwork

                I don't think this is a 'bug', at least the sample workbook you provided isn't. Tableau did what you told it to do, a running sum. When Charlie's data dropped to zero the line of course leveled off, running in place if you will. (If it didn't do this in 6.1, then the bug was in 6.1 not 7.0 Anyway I've attached the fix and here's what I did:

                 

                Special Values.PNG

                And then you'll need to hide special values:

                 

                Special Values-2.PNG

                 

                --Shawn

                • 5. Re: Tableau 7.0 Extending Table Calculation
                  Alex Kerin

                  But, the data didn't drop to zero, it went to null (didn't it?) - I think there should definitely be an option to change this behavior

                  • 6. Re: Tableau 7.0 Extending Table Calculation
                    Shawn Wallwork

                    Alex, the data must have dropped to zero (or NULL), I didn't really check the data, but since the line leveled off I just assumed it went to zero. Either way the calc worked when you hide NULLs.

                     

                    --Shawn

                     

                    Edit: I sort of remember Jonathan talking about NULL being evaluated as less the zero -- not a negative number but the lowest value when doing MIN() calcs. So I'm thinking that's why the >0 calc works, it includes NULLs.

                     

                    Message was edited by: Shawn Wallwork

                    • 7. Re: Tableau 7.0 Extending Table Calculation
                      Alex Kerin

                      Actually, not even Null - missing data. This should be handled under the missing data formatting:

                      null.png

                      • 8. Re: Tableau 7.0 Extending Table Calculation
                        Shawn Wallwork

                        There's a 'missing data formatting'? Guess I missed that section of the documentation.

                         

                        BTW, I'm curious, do you have example of when the current and 'new to 7.0' (according to Tracy) behavior is not a good thing? Alex, sorry I guess I'm somehow missing your point...

                         

                        --Shawn

                        • 9. Re: Tableau 7.0 Extending Table Calculation
                          Alex Kerin

                          No problem Shawn - I just feel that if the data isn't there (it would be different if the dates existed with a value of 0) then lines should not be plotted - let's say it's an error that the dates are missing - unless you were knowledgeable of the data, you may assume that Charlie really didn't sell anything. However, if the line stopped while others continued, you'd suspect there was a problem. When you are removed from the data (as you are in Tableau more so than Excel) I feel that all defaults should be to not show what I consider missing data.

                           

                          This is exemplified by the fact that 7.0 has much better handling of nulls than 6, it's just that this hasn't pulled through to the running sum calc.

                          • 10. Re: Tableau 7.0 Extending Table Calculation
                            Shawn Wallwork

                            I feel that all defaults should be to not show what I consider missing data.

                            Ahhh, now I get what you're saying. There should be a distinction between '0' and 'null' and 'no data' and the default should be more representative of the underlying data 'reality' -- I absolutely agree! I'm giving this 1 Vote up, if you put it in the ideas forum. Thanks for the explaination.

                             

                            --Shawn

                             

                            PS: Why can't we 'branch' these sorts of discussions to the ideas forum?

                            • 11. Re: Tableau 7.0 Extending Table Calculation

                              Thanks for all of the feedback... the discussion provided valuable insight to Tableau functionality, beyond the simple answer to my question.

                               

                              I very much agree with Alex's comment that the default handling of missing data should be to NOT DISPLAY IT.  It is quite disappointing that Tableau changed this functionality in 7.0.  We frequently chart quarter-to-date metrics vs. prior-quarter metrics, and have other 6.1 dashboards where the lines are truncated (as you would expect in the middle of a quarter).  Looks like I will be doing a lot of fixes on existing dashboards.

                               

                              Meanwhile, I have posted this to the Ideas section.

                               

                              BTW, Shawn's solution worked perfectly with my real dataset (which is "slightly" more complex, but cannot be shared).  Nice intuitive formula, too.


                              Thanks again -
                              - Kenny

                              • 12. Re: Tableau 7.0 Extending Table Calculation
                                Chris Fort

                                I just found this post after posting a similar question of my own. Oops.

                                 

                                If this is by design then why do bar charts stop at the last actual data point?

                                 

                                Chris

                                • 13. Re: Tableau 7.0 Extending Table Calculation
                                  Ross Bunker

                                  Hello all,

                                  Thanks for all the feedback and attention to this issue.  I wanted to add a little depth to what's going on here.  In Tableau 7.0, we finally implemented a long-requested feature to 'fill in' missing dates.  While many users have requested this over the years, it's pretty tricky to get right, so we tried to do this in a minimal way (lines and area charts were the focus).  Despite our precautions, it has led to a few unexpected cases like this one.

                                   

                                  One subtlety here is that RUNNING TOTAL is different from normal measures.  Alex, you were suggesting we should default to not showing missing data, and I believe that actually is the default.  The issue here is that when you compute a running total, the value for a given day is the previous day, plus the current day.  If the current day contributes nothing to the total because it is missing or NULL, the value for the current day will just be the same as the previous day.  Hence the straight line.  6.1 didn't have the behavior i mentioned above of filling in missing dates, so there was no 'current day' to compute the running total for, it just stopped.  It is the combination of running total and our date filling that led us to this situation.

                                   

                                  I'm glad Shawn's workaround worked for you Kenny, though it wouldn't necessarily work for everyone.  If you consider the case where you really were missing data for one day in the middle of the year, that solution will break the line at that point.  Since you have all the data, and we are 'helpfully' adding in 'missing' dates that aren't really missing, forcing those values to be NULL works fine.

                                   

                                  We're working on how best to address this in a more permanent way.  This filling in of missing dates was a tricky feature and we're still trying to get it right.  Thanks for your patience.

                                   

                                  :)ross

                                  Ross Bunker

                                  Senior Software Engineer

                                  Tableau Software

                                  • 14. Re: Tableau 7.0 Extending Table Calculation
                                    Alex Kerin

                                    Thanks for the update Ross. Perhaps there should be an option specifically for running total which is to continue the line (or not) on a null.

                                    1 2 Previous Next