
1. Re: Aggregating Table Calculations
Patrick Calnan Sep 10, 2013 1:50 AM (in response to 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 Sep 10, 2013 8:33 AM (in response to Patrick Calnan)Table Calculations can differ because you might want to reuse 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 Sep 10, 2013 8:34 AM (in response to Patrick Calnan)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 Sep 11, 2013 1:48 AM (in response to Matt Lutton)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 Sep 11, 2013 6:57 AM (in response to Patrick Calnan)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 Sep 11, 2013 7:41 AM (in response to Matt Lutton)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

Unit Running.xlsx 2.3 MB


7. Re: Re: Aggregating Table Calculations
Matt Lutton Sep 11, 2013 7:48 AM (in response to Patrick Calnan)I'm at a conference and your Excel file is taking far too long for me to download on their WiFi. How about a screenshot/mockup of the result you hope to achieve in Tableau?

8. Re: Aggregating Table Calculations
Patrick Calnan Sep 11, 2013 7:52 AM (in response to Matt Lutton)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 1 Unit 2 Unit 3 Unit 4 Jan13 2 0 0 4 Feb13 0 1 1 1 Mar13 0 2 0 0 Apr13 0 2 0 0 May13 1 0 1 0 Jun13 2 2 0 2 Jul13 0 0 0 0 Aug13 0 0 0 0 
9. Re: Aggregating Table Calculations
Matt Lutton Sep 11, 2013 12:26 PM (in response to Patrick Calnan)I understand what you want, but was unable to get at the solution in the past 1520 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 Sep 11, 2013 12:33 PM (in response to Matt Lutton)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 Sep 11, 2013 1:14 PM (in response to Patrick Calnan)Is a "Start" when Schedule_Quanity(SUM) switches from "Off" to "On"?
Shawn

12. Re: Aggregating Table Calculations
Patrick Calnan Sep 11, 2013 11:51 PM (in response to Shawn Wallwork)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 Sep 12, 2013 5:54 AM (in response to Patrick Calnan)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 Sep 16, 2013 9:56 AM (in response to Shawn Wallwork)See the attached. The key to this kind of view (outside of doing a bunch of work in the query to precalculate 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 nonNull 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 perunit 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

Start Stop Table Calc jtd.twbx.zip 150.9 KB
