9 Replies Latest reply on Oct 14, 2014 2:28 PM by Phil Roberts

    Table calculation for the recency of events

    David Phillips

      Hi all,

       

      Long time fan, first time poster. I'm having some trouble with a tricky calculation that I hope someone could help with. My dataset has events occurring across multiple series. Think of it as campaign speeches across multiple campaigns. Each record has the:

      • [Name] of the candidate
      • [Start time] of event
      • [End time] of event
      • [City] of event
      • [State] of event

       

      The records are ordered by [Name] and are not overlapping (a candidate is not in two events at the same time).

       

      My challenge is to create a Table Calc that determines the duration since the previous event. My first attempt was [Start time] - LOOKUP(MAX([End time]), -1), computed across [Name], but that isn't working.

       

      I'd greatly appreciate anyone's assistance (Tableau 7 solution, please). Thanks in advance for your time.

       

      -David

        • 1. Re: Table calculation for the recency of events
          Jonathan Drummey

          Table calculation results are dependent on factors like the pills in the view, the sort order, the sparseness of your data, and in some cases even your Mark Type, among other factors, so there's not enough information in your post to be able to give you a recommendation.

           

          Please post a packaged workbook with some sample data, or at least some sample data and a mock-up of your desired view.

           

          Jonathan

          • 2. Re: Table calculation for the recency of events
            David Phillips

            Thanks for the quick response. Big fan, Jonathan.

             

            The attached represents the type of analysis i'm interested in. I annotated a few Name A marks to demonstrate the calculation i'm trying for (again, these are irrespective of Name B and Name C events). I'd like to include [Down-time (before)] as a Level of Detail in the Gantt Chart. On Sheet 2, I'm trying to also access the average down-time before/after events in a given city.

             

            BTW, I've also found that some marks within a Name do overlap (see multiples for Name A, 6/4/2010 6:59pm). I can assume a sort by [Start_Date], with negative down-time being ok.

             

            Is a solution possible with this information?

             

            Thanks again,

            David

            • 3. Re: Table calculation for the recency of events
              Jonathan Drummey

              Hi David,

               

              Thanks! See the attached for what I think is a solution.

               

              For the Gantt chart, I limited the dimensions in the view to Name, Start Date, and City. End Date is in the view with the ATTR() aggregation so it's not part of table calc partitioning. Then the Prior Start Date table calculation has an Advanced... Compute using of Name, Start Date, and City (in that order) sorted by Start Date/Min/Ascending, restarting every Name. This allows Tableau to properly sort the start dates for every name/city/start date combination while partitioning them appropriately. The "gantt workout" worksheet shows the steps I took, "revised gantt" shows the results.

               

              For the crosstab, I put in extra calcs to show all the steps. The key is that Start Date needs to be in the view so we can look at each record to compare start & end dates. So there are table calculations to compute the average down time before and after, all have the same Compute Using of State/City/Start Date, sorted by Start Date/Min/Ascending, Restarting Every City. There's an additional table calc for the Number of Records, that's because with Start Date in the view we'd end up with duplicate results. The "crosstab workout" and "revised crosstab" worksheets show the results.

               

              Jonathan

              • 4. Re: Table calculation for the recency of events
                David Phillips

                Perfect. Thanks so much.

                 

                I understand your solution/explanation except for one component: why is it that you chose to include City in the Compute using? Wouldn't the Prior Start Date output be the same regardless of the City value?

                • 5. Re: Table calculation for the recency of events
                  Jonathan Drummey

                  Hi David,

                   

                  My understanding was that you wanted the prior start date for each Name/Start Date combination, is that correct?

                   

                  The reason City is in the Compute Using has to do with the level of detail of the view and table calcs. In the Gantt Chart neither of the other fields get us to a level of detail where we could aggregate City and still have it work for the Color Shelf - Name obviously doesn't, and there are the cases where multiple cities have the same Start Date. Therefore, City has to be in the view as a non-aggregated dimension, which means that table calculations must have City in their addressing (compute using) or partitioning (everything that isn't addressing). Since we don't want to restart the computation (what partitioning does) on each City, it has to be in the Compute Using/addressing.

                   

                  Jonathan

                  1 of 1 people found this helpful
                  • 6. Re: Table calculation for the recency of events
                    David Phillips

                    I was going to the prior End Date (also, the subsequent Start Date) for each Name/Start Date event combination. Your explanation gave me what I needed to make that happen.

                     

                    Thanks again,

                    David

                    • 7. Re: Table calculation for the recency of events
                      David Phillips

                      One more issue: I'm getting unexpected, and clearly incorrect, results when I try to link this data to a scatterplot of event [Down Time] vs [Duration] using a filter action by the selected City.

                       

                      I'm trying to get the values in the scatterplot to align with the values in the Gantt. For example, in the attached demo, the [Down Time Before] for the five Columbus events should be:

                      • 0.00 (B #1)
                      • 0.00 (B #2)
                      • 0.78 (C #1)
                      • 0.03 (C #2)
                      • -0.04 (C #3)

                       

                      In addition to none of these corresponding correctly in the scatterplot, I can't figure why two of the points are coming across as Null. Do you have any advice for me to get the worksheets in sync?

                       

                      Thanks in advance,

                      David

                      • 8. Re: Table calculation for the recency of events
                        Jonathan Drummey

                        The reason why the scatterplot isn't working is because of the Filter Action and Tableau's order of operations. The measures for the scatterplot are table calculations, which are computed on all the data returned to Tableau based on the regular (i.e. non-table calc) filters in use and the dimensions in the view. The Filter Action on All fields filters on City, Name, and Start Date as regular filters, so there isn't the data necessary to properly compute the times and the scatterplot values come out as Null or the wrong value. Changing the Filter Action to just City doesn't help either.

                         

                        To preserve the results for the table calcs in the scatterplot, the general way to go is to use a table calc filter, which is applied after the table calcs are computed. I tried to create a workaround by using a table calculation for LOOKUP(ATTR([City]),0) and using that in both the Gantt and scatterplot views, but that didn't work either. Tableau will happily base a Highlight Action on a table calculation, and as a specific field will let me choose the table calc for the origin of a Filter Action, but not the target. I've attached a workbook that demonstrates this.

                         

                        Based on that, I can't think of any way to have the user click on a city in the Gantt view and have that filter the scatterplot view. I can think of some workarounds:

                         

                        - Instead of filtering the scatterplot view, in the scatterplot put City on the Level of Detail or Color Shelf and then just use a highlight action. The user will see all values on the scatterplot, and any time they click on a Gantt bar they will get the corresponding circles in the scatterplot highlighted. I set that up in the attached, and this is what I'd use. That way the user could see the whole spread of the data.

                        - Set up a parameter with a value for each city to use for the filtering.

                        - Use Custom SQL or a query to build the Down Time Before/After calcs so you wouldn't be using table calculations at all, then the filter action could work.

                         

                        I don't have a whole lot of experience with Action Filters, maybe Joe Mako or Alex Kerin have some other thoughts?

                         

                        Jonathan

                        1 of 1 people found this helpful
                        • 9. Re: Table calculation for the recency of events
                          Phil Roberts

                          Thank you Jonathan Drummey for a very helpful answer.  Thanks David for asking a good question and putting together the example workbook.  This really helped me with a similar problem.