1 2 Previous Next 20 Replies Latest reply on Sep 17, 2013 12:24 PM by Jonathan Drummey

    Aggregating Table Calculations

    Patrick Calnan

      Hi,

       

      I am still struggling to get my head around table calculations even though I have used several of the excellent on-line resources and found help on the forums here thanks to @Daniel VanderMeers:

      http://community.tableau.com/thread/129314

       

      The problem I now have is how do I aggregate a table calculation. In the attached workbook I have calculated the number of starts over a time period. How do I count the number of starts over a month? The starts are dependent on the datetime on the half hour level.

       

      Also, in the workbook I am confused as to why the is shown as "yellow" for starts even though the start text is not applicable. It is like the start with the color yellow has to be on one column.

       

      Thanks,

       

      PC

        • 1. Re: Aggregating Table Calculations
          Patrick Calnan

          Sorry, but I asked the following question as to why it is shown as "yellow" for starts even though the start text is not applicable.

          This was due to the fact that the Table Calculation was "Compute Along" Units for the color and Table Down for the Text.


          This creates another question as to why Table Calculations can differ within a view?

          • 2. Re: Aggregating Table Calculations
            Matt Lutton

            Table Calculations can differ because you might want to re-use the same calc multiple times, computed in different ways.  This can be a very powerful feature.  As an example, you might want to use a table calc computed along city, state, region, etc. within one view

            • 3. Re: Aggregating Table Calculations
              Matt Lutton

              If you right click on your datetime field, you can choose "Month" to quickly show the data aggregated at this level. 

              • 4. Re: Aggregating Table Calculations
                Patrick Calnan

                Matthew,

                 

                Thanks for your reply. I have right clicked datetime field and aggregated on a monthly basis but it does not aggregate my start time calculation.

                 

                In my very simplistic understanding of Table Calculations does changing the datetime field in this manner change the underlying table structure and hence the calculation?

                 

                Patrick

                • 5. Re: Aggregating Table Calculations
                  Matt Lutton

                  If you can show me the numbers you are expecting as a result, I can try to help you arrive at that.  Table calculations are dependent on the view, so when we change the datetime to month, we need to address that change in our calculation.  I am assuming this is why the results aren't what you expected.

                  • 6. Re: Re: Aggregating Table Calculations
                    Patrick Calnan

                    Hi Matthew,

                     

                    Please find attached a spreadsheet of what I am trying to do.If you look at cells P2:T10 you can see what the ultimate output that I am trying to achieve looks like (so easy in excel....I am trying to get my head around table calculations)

                     

                    Patrick

                    • 7. Re: Re: Aggregating Table Calculations
                      Matt Lutton

                      I'm at a conference and your Excel file is taking far too long for me to download on their Wi-Fi.  How about a screenshot/mockup of the result you hope to achieve in Tableau?

                      • 8. Re: Aggregating Table Calculations
                        Patrick Calnan

                        Matthew,

                         

                        I take it your at TCC13 like every other Tableau user in the US

                         

                        The following table shows the result that I want to get

                         

                        # of Starts by Month
                        Unit 1Unit 2Unit 3Unit 4
                        Jan-132004
                        Feb-130111
                        Mar-130200
                        Apr-130200
                        May-131010
                        Jun-132202
                        Jul-130000
                        Aug-130000
                        • 9. Re: Aggregating Table Calculations
                          Matt Lutton

                          I understand what you want, but was unable to get at the solution in the past 15-20 minutes of looking at it.  If I find more time, I'll try again. In the meantime, perhaps Shawn Wallwork can take a look in the meantime.


                          • 10. Re: Aggregating Table Calculations
                            Shawn Wallwork

                            I'm absolutely interested in anything coming out of the #TCC13.In the meantime I'll take a look at this Q.

                             

                            --Shawn

                            • 11. Re: Aggregating Table Calculations
                              Shawn Wallwork

                              Is a "Start" when Schedule_Quanity(SUM) switches from "Off" to "On"?

                               

                              --Shawn

                              • 12. Re: Aggregating Table Calculations
                                Patrick Calnan

                                Hi Shawn,

                                A start is defined as when the previous half hour the Unit showed as zero and where the current half hour the unit is greater than zero.

                                 

                                My problem is that I want to do the table calc on the half hour level but aggregate this to sum on a monthly/yearly granularity. I dont understand how a table calc works if the basis of the table changes

                                 

                                PS I hope you have noticed how my manners have improved.

                                 

                                PC

                                • 13. Re: Aggregating Table Calculations
                                  Shawn Wallwork

                                  Patrick I did notice! I'm trying to clean up my manners as well. I worked on this a bit yesterday and couldn't work it out. Maybe now that the conference is over we can get Jonathan Drummey or Joshua Milligan to show us how to do this.

                                   

                                  Cheers,

                                   

                                  --Shawn

                                  • 14. Re: Aggregating Table Calculations
                                    Jonathan Drummey

                                    See the attached. The key to this kind of view (outside of doing a bunch of work in the query to pre-calculate stuff) is to use nested table calculations. I set up the Start Flag calc to return a 1 if a start is indicated, Null otherwise, and tried to optimize the order of operations in it to be fast. This has an advanced Compute Using of the Month of datetime & datetime so it partitions (restarts) on each unit. Then the Total Starts calc sums up all the Start Flags for each month/unit by having a *nested* Compute Using of just the datetime, with the inner Start Flag still having the advanced Compute Using. I created two versions of the Total Starts calc, an initial one for verification then an optimized one that only returns a single value for each month. You can see everything in the "workout" worksheet, which uses an ATTR(datetime) on Rows with the real datetime Dimension on the Level of Detail to avoid unwanted date padding by Tableau (that can suck up all the available memory). The "view" worksheet has the final view, a copy of the Total Starts (optimized) calc is on the Filters Shelf set to Filter for non-Null values, this gives the view the desired display and improves performance.

                                     

                                    Tableau's power in general comes from the fact that in each worksheet we pick the dimensions (buckets) and measures (the things that go in the buckets) that we want to display, by putting them on Columns, Rows, Pages, and the Marks Card. What table calculations let us do is take that to a further level of capability (and abstraction) by letting us aggregate across buckets in a given view. Effectively, table calculations give us additional sets of buckets that can combine the original buckets in almost any way we want, for example the monthly average of a daily sum of sales, or the monthly per-unit total of the # of starts from every 1/2 hour period. So we build a view with the buckets (dimensions) that we need to get accurate measure results, then we can build the table calculations to aggregate across those, and finally do some cleanup (filtering) so we only show the results we want.

                                     

                                    The Compute Using settings of a table calculation determine what distinct combinations of dimensional values make up an "address" or "row in the partition" that Tableau will compute results for, the partitioning is made up of all other dimensions in the view. So in this case, the Start Flags calculation needs to be computed along the datetime, ignoring the Month (so the Month is part of the addressing), and restarted (partitioned) by the Unit. When it's time to sum up all those Start Flags, again we use the datetime for the addressing, but leave the Month and Unit as part of partitioning so the sum is restarted for each new Month/Unit combination.

                                     

                                    Hope this helps!

                                     

                                    Jonathan

                                    1 2 Previous Next