background preloader

Table Calculations

Facebook Twitter

Want to Learn Table Calculations? Here’s How! RHSD - The Anatomy of a Nested Table Calculation. The twittersphere & inter-webs are abuzz with help understanding Tableau Table Calcs.

RHSD - The Anatomy of a Nested Table Calculation

Among the more recent, these are my favorites: And fully two years ago now, Jonathan started to compile this list of resources for all skill levels: Want to Learn Table Calculations? Here’s How! Moving average of running total table calculation. Breakthrough.

moving average of running total table calculation

After I modified Shawn's version of the sales workbook with my forumlas, I found the results were almost the same. Then I realized that's because there are order events for every day, and the date format is set to date, which means the columns of the table are exactly days, so the window calculation units are days. Whereas in my data, my events are plotted in datetime rather than date, which means there is not a 1:1 relationship between columns and days, hence the need for all my formulas. When I switched my data from datetime to date, everything got smoother and I had events for almost every day so my units are almost 1:1.

My formulas are still required for when there are days with no data, though if this is rare, and you're only concerned with window averages, the effect is minimal. Here's what my data looks like now, compare to the first picture I posted above. Jonathan's Teachings re: Table Calculations &am... This came from a thread I didn't want to lose: Taking a step back here, the question is about comparing across records in the underlying data in order to filter out Email IDs that belong to list 123, then count the remaining Email IDs per List ID.

Jonathan's Teachings re: Table Calculations &am...

This kind of problem is a great practice for me to explain, so I'll take a little extra time to walk through the steps involved to reach a couple of solutions. A distinct record (row) in the data is made of the Email Address and List ID. So if we want to exclude certain Email IDs based on the presence of a List ID for that record, we have to look across records. In order to make Tableau do that (there's no single magic function to do that kind of filtering, like the other BI tool you mentioned, Roopa) we need to work with the three calculation levels in Tableau: - Record (or row) level. . - Aggregate. Jonathan's Teachings re: Table Calculations &am... Calculating sum of requests within a zip code r... Hi Florian, To understand what is going on here requires some understanding of how Tableau computes totals.

Calculating sum of requests within a zip code r...

Joe Mako shared an nice perspective on this with me and it is a very helpful perspective for this example, so I will do my best to paraphrase (I pinged him just in case I got something wrong). Think of the grand total as a separate worksheet, where the only difference is that the dimension you are totaling across isn't present. In your situation, that means zip code isn't there. The problem with that your calculations, in particular the filters, depend on the full granularity of zip code. My approach to forum questions sometimes reminds me a bit of the tv show Mythbusters. GROUPBY DISTINCT MAX Tableau 9. For more info on the three levels of calculation, I suggest you watch this Think Data Thursday on Setting up for Table Calculation Success, most of it is very applicable to working with Level of Detail calcs: Fundamentally, there are three levels of calculation in Tableau: - record level: The calc is computed for each and every record in your data source. - aggregate: This is SUM(), MIN(), MAX(), etc. and the calculation is computed at a specific granularity.

GROUPBY DISTINCT MAX Tableau 9

In the case of a view, the granularity uses the dimensions in the view (this is a simplification when it comes to blending) - in SQL terms, all your dimensions are part of the GROUP BY of the aggregation. For a conditional or top filter or computed Set, the granularity is the chosen dimension. Table Calculations - Why apply them 'By Cell'

A question I was recently asked by an in training Tableau User was ‘Why would I apply a table calculation by cell?’.

Table Calculations - Why apply them 'By Cell'

LOD Calculations

Top 10 Tableau Table Calculations. This page shows 10 powerful examples of Tableau's Table Calculations.

Top 10 Tableau Table Calculations

Most require writing a simple formula of some kind. RHSD - Master Tableau Approach. In an earlier post, titled Master Tableau Concepts, I have summarized and added color to a talk by Joe Mako in which he highlighted the advanced concepts that a master practitioner can use to achieve flow by working with instead of against Tableau.

RHSD - Master Tableau Approach

This was an excellent talk which he also gave as a repeat at TC14. In this new post, Master Tableau Approach, I’ll summarize & add color to a TC14 session from Bethany Lyons and Alan Eldridge, titled Jedi Level Calculation Techniques. Today's topic is the thought process used by a master practitioner when deciding which technique to employ when answering a complex question using Tableau. While introducing his own TC14 session, Dashboarding at the Edge of Impossible, Dustin Smith described the Tableau Jedi not as the person who knows every tip and trick. He instead described a Jedi as the person who knows how to best approach the problem. Rank Question. True.

Rank Question

It is always interesting to see several solutions to a problem. This is curious because of the different partitioning required. Using a table calc across date field turns on domain completion (densification) so you get the same set of dates for every employee. This is a problem (and often a very confusing one), since the max date is then the same for every employee but since this isn't necessarily a record in the original datasource you end up getting a null for sum([Result]). Matthew's solution works around this by formally including the Employee dimension in the partitioning, but restarting the calculation for each employee. For the tie breaker, I added a third solution that uses the same partitioning as Matthew's, and also uses Rank.

A Jedi (Filter and Table Calc) Trick. Let’s say we would like to rank our Sub-Category of Products by Sales and have the ability to remove/hide certain Sub-Categories without this affecting our rank calculation or average reference line.

A Jedi (Filter and Table Calc) Trick

Well, this is not as straight-forward as it seems! Try it yourselves in the below viz, if you de-select the Office Machines from the filter, the rank and the average sale reference line will both recalculate. This is actually working as intended as when we remove the item from the Quick Filter we are actually removing it from the query that Tableau sends to the datasource. Average Number of Records per Hour per Weekday. Hi Bill, I'm working on my Think Data Thursday presentation for next week and found your post and it's a really good example of some things I'll be presenting, so I'm going a little long in my write-up here. I also do appointment analysis, so I'm not unfamiliar with this kind of problem. In Sheet1 of your view you had turned off Aggregate Measures, so what Tableau is doing in SQL terms is something like: SELECT weekday(date), hour(date), row_num, Number of Records. At the Level – Unlocking the Mystery Part 1: Ordinal Calcs.

There was a Tableau forums thread on At the Level awhile back where Matthew Lutton asked for an alternative explanation of this somewhat puzzling table calculation configuration option, and I’d promised I’d take a swing at it. Plus, I’ve been deep into book writing about shaping data for Tableau, and a taking a break to write about obscure table calc options sounds like fun! (Yes, I’m wired differently.) Table Calculation Fundamentals.pdf. Tip0911.pdf. Want to Learn Table Calculations? Here’s How! Transition Matrix. I'm with Noah. Doing the work outside of Tableau to give yourself a Row ID that you could join on or even to set up some slightly-complicated SQL to do the same using a date will make life so much easier in Tableau that I believe it's worth the effort. I'll explain how the LOOKUP() is doing what it does in your Table Calc view, then explain why a working table calc solution is more difficult to put together.

Tableau makes working at different levels of granularity really easy, right up until the point that we need to pay attention to them and then it's not much help. In the Table Calc view, there are two levels of granularity that are important, the granularity of the raw data (where Date & Name are the dimensions that determine what makes a unique record) and the granularity of the view (the distinct combinations of values of the dimension(s) are on Rows, Columns, Pages, and the Marks Card). Want to Learn Table Calculations? Here’s How!

How to Have Sets with Your Secondary (Data Sources) Tableau Sets can turn incredibly complicated interactions into a few mouse clicks that can reveal patterns in your data. However, if you’ve ever created a Set in a secondary data source and tried to use it across a blend in a primary, this is what you see: Lots of grayed out items that can’t be touched. It’s like you’re at a party and have just met the hot-babe-of-appropriate-gender-of-your-dreams and they are totally into you, but only at the party, and not back at your place. However, like many things in Tableau, with a little creativity you can get what you need, and there is a way where you can have your Sets anywhere you want. If you’re not familiar with sets, then I suggest you start with Hot. For this post I’ll attempt to avoid any more jokes about sets and show you how you can get jiggy with use sets from blended secondary data sources, and probably learn a bit more about data blending on the way.

Table Calculation Fundamentals.pdf. Table Calculation Functions. Table Calculations. From comments by Joe Mako: 1. First step in table calcs is what kind of pills you have: a. aggregated continuous b. aggregated discrete c. dimension continuous d. dimension discrete 2. Help me understand "At the Level" and "Restarti. Hi guys, Thanks for your patience, Richard. Sometime i'll write that tutorial. Help me understand "At the Level" and "Restarti. Version 6 table calcs - understanding a few basics. Joe, Add Sparklines to a Tableau Table. Want to Learn Table Calculations? Here’s How! Comparing Each Against Each Other: The No-SQL Cross Product. Getting the Total for Count Distinct. Table Calculations. Table calculations are computations that are applied to the values in the entire table and are often dependent on the table structure itself.

For example, in a sales environment, you can use table calculations to compute the running total of sales across a specified date range, or to compute each product's contribution to the total sales in a quarter. Quick Table Calculations. You can add common table calculations to your view using the Quick Table Calculations menu item on the field context menus. Addressing and Partitioning. The addressing fields define what part of the table you are computing along. Groundwork for Custom Table Calculations - InterWorks - Tableau Software & Business Intelligence Consulting. Space: Forums. Top N, with everyone else as "Other" Question on how to create a product velocity ca...

Addressing and Partitioning. Table Calculations. Top 10 Tableau Table Calculations. Table Calculations. Advanced Training - Table Calculations.