1 / 20

Grand Totals and Subtotals: How They Work, How to Make Them Work for You

Grand Totals and Subtotals: How They Work, How to Make Them Work for You. Jonathan Drummey Quality Management Data Analyst Southern Maine Medical Center j onathan.drummey@gmail.com. Teaching you to fish: How Tableau computes grand totals and subtotals

gino
Download Presentation

Grand Totals and Subtotals: How They Work, How to Make Them Work for You

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Grand Totals and Subtotals:How They Work, How to Make Them Work for You Jonathan Drummey Quality Management Data Analyst Southern Maine Medical Center jonathan.drummey@gmail.com

  2. Teaching you to fish: • How Tableau computes grand totals and subtotals • TOTAL vs. WINDOW_ and RUNNING_ table calcs Giving you the fish: • How to customize your own grand total • How v8 is different

  3. Each Grand Total and Subtotal is a separate computation of the measure at a different level of aggregation

  4. TOTAL (and Grand Totals and Subtotals) perform a separate computation of the measureat a different level of aggregation WINDOW_ and RUNNING_ functionsaggregate the original aggregation

  5. Measure Aggregations

  6. Measure Aggregations

  7. Within the Grand Total computation, the Grand Totals effectively removes all discrete dimension pills from the Rows (for Column Subtotals) or Columns Shelves Each Subtotal effectively removes all discrete dimension pills to the right of the subtotal’ed pill from the Rows (for Column Subtotals) or Columns Shelves

  8. Giving you the fish: Techniques for Customizing Grand Totals and Subtotals • Two worksheets and a dashboard • Test for MIN/MAX • Test for TOTAL(COUNTD()) • Increasing Level of Detail in the Total Computations • Custom SQL

  9. Two worksheets and a dashboard Advantages • Don’t have to mess around with custom calcs (much) • Will work with any data source Disadvantages • Fiddly layout bits • Any filters or parameter-based calculations will need to be applied to both worksheets. • Any selections in the data worksheet to exclude marks won’t be reflected on the grand total worksheet. • Can’t deal with expanding/collapsing hierarchies.

  10. Test for MIN/MAX Advantages • Really simple test • Will work with any data source Disadvantages • Can’t get an aggregate of an aggregate • Fails when there is only one row in the category • Can’t deal with expanding/collapsing hierarchies

  11. Test for TOTAL(COUNTD()) Advantages • Can be used when there is only one row in the Category Disadvantages • Requires data source that supports COUNTD() • Doesn’t handle aggregates of aggregates • Can’t deal with expanding/collapsing hierarchies

  12. Increasing Level of Detail in the Total Computations Advantages • Supports aggregates of aggregates Disadvantages • Every measure displayed needs to be altered • Can’t deal with expanding/collapsing hierarchies • Can’t have custom sub-aggregations like the MIN/MAX technique • In v8, need to control mark stacking

  13. Custom SQL Advantages • Most flexible • Allows for totals to be on top or left Disadvantages • Potentially doubling data • Not the same formatting options • Every measure & dimension has to evaluated and potentially custom fields created

  14. Teaching you to fish: • How Tableau computes grand totals and subtotals • TOTAL vs. WINDOW_ and RUNNING_ table calcs Giving you the fish: • How to customize your own grand total • How v8 is different

  15. Advanced Topic #1 – Hide • We can hide rows and still have the subtotal row show up, but can’t use table calc filter

  16. Advanced Topic #2 - Annotations • Annotating Subtotals • http://public.tableausoftware.com/views/annotatingsubtotals/Option5-AnnotatingSubtotals • Annotating Views with Totals • http://public.tableausoftware.com/views/annotatingaviewwithtotal/1_labelmaxmark

  17. TBD Advanced Topic #3 – Custom Grand Totals and Subtotals of Table Calcs • Aggregates (grand totals and subtotals) of aggregates (table calcs) of aggregates • need to combine tests for row with the increasing level of detail

  18. Vote to Make Our Lives Easier http://community.tableausoftware.com/ideas/1232 Totals and Subtotals based on aggregate of what is displayed

More Related