Category: Uncategorized

Table Calcs – TC 17 Takeaways

Just got back from Tableau Conference 2017! I’m putting together a teach-back for work, and figured I would share it here too!

At the conference I attended these Hands On sessions:
Advanced Calculations – 8 hour paid class
Table Calcs for the advanced analyst
Tableau + Python = ❤
LOD Expressions vs. real world

You can access workbooks and other materials from the sessions for free at TClive.

Resources - Copy.PNG
Screenshot of the TC live Resources Page with relevant Resources saved

Table Calc Menu - Copy - Copy
All Table Calculations have three basic parts

  • An aggregated field (Blue)
    • Values used in the calculation
  • Calculation Type  (Orange)
    • Calculation to Perform
  • Compute Using  (Green)
    • Scope and Direction of Calculation

Some Calculation Types have additional options, allowing you to change the order, computation, or add a secondary calculations.

Understanding the Compute Using menu is key

Compute Using determines Scope and Direction
Michel Sandberg’s site datavizblog.com has a great guide for scope, including some awesome visuals that I’ve borrowed below

Scope - Copy.PNG
Source: Data Viz Blog – Tableau Calculations Scope and Direction by Michael Sandberg

Scope:

Scope2 - Copy.PNG
Source: Data Viz Blog – Tableau Calculations Scope and Direction by Michael Sandberg

Direction:

Direction - Copy.PNG
There’s also Across then Down, which looks like the inverse of Down then Across.  These directions are shown at a Table level of Scope.

Note – Compute Using Options handle Null values inconsistently. See Workbook

This slideshow requires JavaScript.

Calculation Assistance is super helpful! It will highlight the scope of your Table Calc, and for most marks, the order of the values. The value order might not appear if the mark type is text.

Calculation Assistance - Copy

 

Compute Using Specific Dimensions allows more control over scope and direction.

Selecting Specific Dimensions enables a box with a checklist of dimensions from the view. Unselected dimensions act as Partitioning Fields.

Specific Dimensions - Copy
Source: VizWizTable Calculations Overview by Andy Kriebel

 

The order of the selected dimensions determines the direction of the Table Calculation. Drag and drop dimensions within the box to re-order.

This slideshow requires JavaScript.

If you use Specific Dimension, you may need to manually update the settings when adjusting the dimensions in your view.

Using Table Calcs in Calculations

After creating a table calculation, drag it into the data pane to turn it into a calculated field. From there, you can edit the calculation and add on to the calculation.

Calculation - Copy

Scope and direction will still be adjusted through the Edit Table Calculation menu.

Using Table Calculations as Filters

Order Of Operatons - Copy

Once you turn a Table Calc into a Calculated Field, you can use that field as a filter. Unlike any other filter, Table Calculations must be completed before Table Calc filters affect the view. This means that Table Calc filters are uniquely suited for situations where we need a subset of the underlying values to feed into Table Calculations, but don’t want to actually see them in our view.

For example, this Table Calc is comparing quarterly sales to the previous year.

Filter - Copy

The Table Calc for 2012 is null because there’s no 2011 data to calculate off of. Filtering Order Date to remove 2012  would prevent the Table Calcs from using 2012 values to generate 2013 results. In this case, we could hide 2012 from the view, but if the view had interactivity that impacting the starting point, a Table Calc filter would be a more dynamic choice.

 

 

 

 

Advertisements

Workout Wednesday – Unemployment

I wasn’t feeling the Makeover Monday dataset for week three, so I did Workout Wednesday instead! (Love Emma’s approach of Trump v handlers tho!) The original required a worksheet for each state, which sounded *NOT FUN* so instead of sticking to the original challenge I got as close as possible using a single trellis chart. 

First I used dateparse on the years, and then created calculations for state and national unemployment rates. By fixing the national rate to the year, we can get them to appear on the same chart. (Note – remember that states have different populations, so don’t use the average of the state’s averages!)

Next I replicated the chart format using a single state. The line was simply the difference between state and national rates. Then I created a calculated field for the endpoint (if last() = 0 then [measure]) and added it via synchronized dual axis. Add a T|F calculation to color and you’re good to go. There was a lot of formatting. The pink shading was done using a reference band, and I used Times New Roman to approximate the original font. The trickiest part was getting the numbers to appear in the right format. ([Measure] > Default Properties > Number Format > Custom and then enter this: “+” #,##0.0%;”-” #,##0.0%;)

Finally, I built the small multiples grid. I had done this before with the lyrics makeover so this was a snap.

It was frustrating to not be in control of the headers, spacing, and axes of the small multiples. While it’s /possible/ to tweak these elements by creating separate worksheets, it isn’t practical or scalable. This is one of the few times I’ve felt truly limited in Tableau and would have preferred working in a platform with less structure and more control.

Data Science

For the past month I’ve been working through Microsoft’s Data Science curriculum on edX. I’ve completed Dat101 (intro to Data Science) and Dat201 (Querying with Transact SQL). Next up is DS101X (Columbia’s Statistical Thinking for Data Science and Analytics).  

So far the content has been great. The SQL course was surprisingly thorough. The Excel section of the intro course was mostly review, but even it had a few new things!  The courses are completely free, though you can give them money for an official certificate.

Python vs Java

I recently completed the JavaScript and Java courses on Codecademy*. (Tableau has a JavaScript API that can be harnessed to create really cool stuff.) But, as soon as I figured out Java, I needed to brush up on Python for an upcoming Data Science course.

Transitioning between the two is notoriously tricky. I found myself adding so many unnecessary characters that I decided to build a table to look at them side by side.

 

Python Java
Comment # //
Multiline Comment “”” /*
Boolean True true
Character n/a ‘A’
String “A” or ‘A’ “A”
x = 1

x = “A”

Legal illegal
Concatenation x = 3

s = “The answer is ” + str(x)

int x = 3

String s = “The answer is ” +  x;

Functions def square(x):

return x*x

public static int square(int x){

return x*x

}

If if x > 0:

  print ‘positive’

elif x < 0:

  print ‘negative’

else:

  print ‘zero’

if (x > 0) {

   System.out.println(“positive”);

} else if (x < 0) {

System.out.println(“negative”);

} else {

System.out.println(“zero”);

}

Boolean Operators and or not &&, ||, !
Loops for w in words:

   w = w.upper()

   print w

for i in range(2, 9, 3):

   print i

for (String w: words) {

   w = w.toUppercase();

   System.out.println(w);

}

for (int i = 2; i < 9; i +=3)

   System.out.println(i);

Dictionaries map = dict()

map[‘Jose’] = ‘773-000-1234’ map[‘Mary’] = ‘312-555-9999’

print map[‘Jose’]  

for key in map:

   print key

   print map[key]

Map<String, String> map = new HashMap<String, String>();

map.put(“Jose”, “773-000-1234”);

map.put(“Mary”, “312-555-9999”);

System.out.println(map.get(‘Jose’));

for (String key : map.keySet()) {

    System.out.println(key);

    System.out.println(map.get(key));

}

From <http://anh.cs.luc.edu/331/notes/JavaVsPython.html>

*The new Learn ___ courses seem crappy. The projects are behind a paywall, and the exercises seem much, much easier than legacy ones.

Hello World!

This blog exists to show off my non-confidential work, and to share the resources and tips that I use. Expect to see a lot of data analysis and visualization using Tableau and Excel, along with an occasional programming lesson or puzzle.

Thanks for visiting
-GH