Gold Study Guide

Question 1

When I add many fields to my rows shelf, some rows start going vertical. As I add more, the labels start getting concatenated. Can I fix this?

Answer 1

You can adjust Tableau from taking these actions by setting default table options. (Analysis  Table Layout  Advanced, and increase the number of row and horizontal row labels.)ALSO: You can perform some of the following actions:• Resize the label rows (click-drag) to provide more room• Choose to “Rotate” the labels (right-click a label)• Edit the aliases of the label values to make them shorter.• Format the font for the labels to make them smaller• Un-Choose “Show Header” for some field labels• Choose “Hide Field Labels for Rows”• Don’t place so many rows onto your sheet – it’s not best-practice for visual analysis!!

Question 2

How does unchecking ‘AnalysisAggregate Measures’ differ from converting a measure to a dimension?

Answer 2

A dimension returns all distinct values of the field. “Dis-aggregate” means that you want to return each row from the underlying database. Example: 1,1,1,1,2,3. When set to a dimension there would be 3 marks, as a disaggregate measure, 6 marks.This becomes very important when using reference lines, trend lines or other secondary calcs. An ‘average’ ref line on the dimension would yield a value of 2, but on the disaggregate data the value would be 1.5.

Question 3

What is an ‘Attribute’ (ATTR)? How does it differ from dimensions and measures?

Answer 3

An attribute is an aggregation that can be applied to Dimensions. This enables the dimensions to be removed from the level of detail, but still be displayed. The functionality is useful for excluding a dimension from a table calc or allowing dimensions from secondary data sources. Returns the value of the dimension if it only has a single value for all rows in the group, otherwise it displays an asterisk () character. Null values are ignored.An attribute is equivalent to the formula: IF MAX([Field])=MIN([Field]) THEN MAX([Field]) ELSE“” END

Question 4

Can I connect to a stored procedure?

Answer 4

Sometimes, but this is not often needed. Due to Tableau’s advanced calculation, filtering and security capabilities, most customers find that they can accomplish more with tableau connecting to raw data than by executing a stored procedure.The stored procedure or User defined function (UDF) must return a table. The reason for this is that all subsequent actions in Tableau will be sent to the database as queries in a ‘sub select’. For Example:Select [Region] From ([my stored procedure result]). Most databases don’t support this. When they do, this effectively means that the stored procedure is executed every time an action is taken in Tableau. This typically defeats the benefits of a stored procedure. A much better approach is to first execute the stored procedure into a table or file and then connect Tableau to the results.

Question 5

Does Tableau have any API’s or scripting?

Answer 5

“What is it that you are trying to accomplish?” We have embedding and URL actions that solve a lot of these needs. Other items are often solved with existing functionality such as table calcs or actions that will help satisfy their requirements. We also have a Javascript API.

Question 6

Your website mentions the VizQL language. How do I program in this or modify the VizQL?

Answer 6

Currently the VizQL is generated only by using the Tableau Desktop interface. It can be manipulated through the Tableau Server web interface by Interactors. It is not possible to see the VizQL or create it directly.

Question 7

How do I group thousands of items together?

Answer 7

Best chance is to use a calculated field with a programmatic condition in these “thousands of items” such that the cardinality is low (less than 100). For measures, rounding using a calc field is useful. For dimensions, rounding using or LEFT or RIGHT functions, etc…

Question 8

Can I save a filter for reuse? For example I manage 12 of the 5000 products we have.

Answer 8

Yes. Create the filter, and then choose “Create Set” from the filter’s context menu

Question 9

If I have a calculated field that includes members from two blended data sources, will the results be included when I create an extract?

Answer 9

Since extracts are specific to each data source, the results from the calculated field will not be included in the data extract (i.e. it will not be optimized). However, the calculation will still work fine.

Question 10

How do I move a sheet from one Tableau workbook to another?

Answer 10

Bookmarks. Save one from the source workbook. Open the destination book, choose that bookmark, save the file. (Note: you cannot bookmark a dashboard)

Question 11

Can I move dashboards from one workbook to another?

Answer 11

No.

Question 12

What is the polygon mark type and how do I use it?

Answer 12

Polygon mark type is advanced and used to describe geographic areas and other areas of measurement. They typically require coordinate data in the underlying database. Armed with the right set of coordinate data, polygon marks – in conjunction with the PATH shelf – can describe two-dimensional areas. Any time you have all of the coordinate data to describe ANY area (state, election regions, etc), you can accurately use the polygon mark type to show these boundaries.

Question 13

What are the two main types of Joins?

Answer 13

Joins require a condition to evaluate how a row in one table matches a row or set of rows in another table. The fields used in this condition are called ‘key’ fields.Inner join: a join of two or more tables that keeps only that rows where the key is common to both tablesOuter join: is further defined as left outer join or right outer join. These includes all rows from the table on the left (or right) side of the join condition regardless of whether a match was found on the right (or left) side.

Question 14

I created a join and most of my data is missing. How do I fix this?

Answer 14

An outer join will always include the records of the primary table (i.e. left outer join will always include data from the ‘left’ table, even if no match is found in the right table.) An example is a transaction header table which is related to a transaction line-item table. For dimensional analysis purposes, you would want all header rows to be returned, even if there was not always a specific line-item related to it.Select * from TransactionHeader LEFT OUTER JOIN TransactionLineItemsOn TransactionHeader.LineItemID = TransactionLineItems.LineItemID

Question 15

What is Join Culling?

Answer 15

Because joins cost time and resources to process on the database server, we don’t really want to use every join that we declared in our data source at all times. Join Culling allows you to skip the join when the query does not require it and provides increased performance. To do this, you must have relationships setup in your database between the tables you’ll be joining in Tableau. Inner Joins will work best for Join Culling.

Question 16

How do I do Sparklines?

Answer 16

Create a line chart.Edit the axis to not include zero.Hide the axisMake the rows really tiny

Question 17

How do I create a chart the shows the budget compared to actual?

Answer 17

Try a bullet graph first. IF that doesn’t satisfy, then:Color Answer: Steps: add “budgeted” to ROW, add a date measure to column. Then, add second measure (e.g. “actual”) to Y-axis (i.e. the row axis where “budgeted” was already placed). Tableau intelligently determined that COLOR is the best-practice in this case.Side by Side Answer: Follow the steps for Color Answer, and drag Measure Names to the Column Shelf next to your Date. This allows different measures to appear side by side.

Question 18

How do I show the rank of my products?

Answer 18

1. Create a calculated field: index()2. Add this to your sheet and change it to discrete.

Question 19

Can I create a chart that shows the trailing 12 months revenue?

Answer 19

Yes, this is easy to do in Tableau. You can use a table calculation for this, as well, you can use the built-in totals feature. If you have “month” as a dimension you can turn on grand totals against this dimension.Answer one: use relative date filters to filter a single sales bar down to the last 12 months (don’t add date to the columns). Answer two: Use a Table calc to use a Moving Calculation for the sum of the last 12 Months. This one is even better as this is simple for Tableau, and hard for other products.

Question 20

Can I create Control Charts?

Answer 20

Yes. Control charts are often used in SPC (Statistical Process Control) and six sigma (6σ) efforts or other quality control. It is basically a time series analysis with trends and reference lines. Tableau makes them very robust with one exception: We can exclude the outliers completely, but cannot exclude them from the reference line calculations while still displaying them on the chart.See the knowledge base for more details.

Question 21

Can I do a Pareto Analysis?

Answer 21

Yes (this is the quick way, a more complete answer is in the knowledge base): Place a dimension on the column shelf. Sort it by descending.Place a measure on the rows shelf. Duplicate it so there are two measures.Set the 2nd one to be a Running Total with a secondary calculation of Percent of Total.Dual Axis these two measures.Set the first measure to be a Bar and the second measure to be a Line.

Question 22

I have a view with MY(Order Date) on Columns, SUM(Sales) on Rows, with three different colored lines – one for each Product Category. I want to animate the lines over time, and show the history so I can get a view like the following where the lines will appear for each month:

Answer 22

Drag Date to the Pages shelf and to columns. In order to get the lines to produce over time, the Mark type needs to be changed from Line to some other option, such as Shape, Circle, even text. Then check Show History on the Pages card, access the drop-down menu, Show History for All and select Trails.

Question 23

Can I use dimensions from the secondary datasource to roll-up my primary data? How can I create a primary group from a secondary data source?

Answer 23

Yes. Kind of.An example is if I have sales and people in my primary and people and their territory assignments in the secondary. How do I roll-up my sales (primary) by territory (secondary)?Once your data blended view is complete you can right click on your field you wish to group from your secondary source and select “Create Primary Group.” This will populate your primary data source with a grouped field based on values shared in both sources.

Question 24

Setup a view that displays only a list customers whom purchased from BOTH Furniture and Office Supplies in the past.

Answer 24

Since no row of record contains information on about a customer whom purchased on multiple product categories, this is a filter based on aggregated results – this suggests using the “conditions” tab in filter on [Customer Name]Use the formula: (MAX(IIF([Product Category]=”Furniture”,1,0))+MAX(IIF([Product Category]=”Office Supplies”,1,0)))=2How this works: • MAX(IIF([Product Category]=”Furniture”,1,0))Show me the value “1” if Customer ever had bought something from Furniture• MAX(IIF([Product Category]=”Office Supplies”,1,0))Show me the value “1” if Customer ever had bought something from Office Supplies• (MAX(IIF([Product Category]=”Furniture”,1,0))+MAX(IIF([Product Category]=”Office Supplies”,1,0)))=2Only show me if both of the above formulas returned “1” or another words 1+1=2

Question 25

Can I use Tableau to discover which products were frequently purchased together?

Answer 25

Yes. This is commonly referred to as a Market Basket Analysis. First, identify the unique identifier (Customer name, ID number, Business, etc.) and the category in question(Product category, Business segment, Academic Course, etc.). Self join tables approach: Go to Edit Tables on the data connection and choose to Add a Table. Select the table with the data we will be needing and choose to Join the Table. Add two join clauses: Join the identifier to itself, and join the category in question to itself where the relationship is (not equal).

Question 26

How about the top 5 products that I sold this year?

Answer 26

Yes. Filter on current year, then add this filter to the “context”. Reason: context is created prior to filtering to the top 5.

Question 27

How about the top 5 in each region?

Answer 27

Yes. Create an index() calculation and add it to the sheet. Edit it to restart for each region.

Question 28

What about how the top 10 products and regions are performing?

Answer 28

Yes. Make a set of product and region, place this new set on row, followed by product and region. Sort on the set. Then hide headers:

Question 29

How do I make a box plot?

Answer 29

Many customers prefer our box plots to the standard definition because we will show you all outliers and plot density throughout the range, not just the whiskers (though you can get that as well).See the knowledge base for examples.

Question 30

Can I find the purchase history of customers that purchased this month?

Answer 30

1. Manually filtering2. Dynamic sets from dimensions3. Manual sets from marks/members4. See also: cohort analysis.

Question 31

Every day, I want to open Tableau and see how yesterday’s top 3 sales people have performed over the last 6 months.

Answer 31

Similar to above, use dynamic sets. Once created, this set can be a filter and be used against any date range. You need to peg it using “add to context”.

Question 32

How do I apply weights when working with my survey data?

Answer 32

If you have a weight field in your data set you can apply this weight with a simple calculated field. [Weight]*[Measure].

Question 33

How do I find the average sale per order?

Answer 33

Calculated field. Given a dimension “Order” and a measure “line item sale amount”Sum([line item sale amount])/Countd(Order)

Question 34

Tableau only created equal sized bins, but I want mine to be 0-10, 10-50, 50-100, 100-1000, and greater than 1000.

Answer 34

Instead of using built in binning, use a calculated field with an IF statement. Something like:IF [Sales] > 10000 THEN “LARGE” ELSEIF [Sales] > 5000 THEN “MEDIUM” ELSE “SMALL” END

Question 35

Your bins are great! But I have just a couple of things that are bigger than 1000, how do just create a bin that is >1000 and not have to define all the intermediary bins?

Answer 35

1. IF [Sales] > 5000 THEN 5000 ELSE [Sales] END2. Then, bin this calculated field3. Place on shelf4. Edit alias for that last group (e.g. ‘>= 5000’)

Question 36

Is there any way that we can dynamically change the size of our bins?

Answer 36

Yes, Tableau makes this very easy to do by allowing you to create parameters directly in the bin menu.

Question 37

I have a date field that looks like 20070326. Tableau doesn’t recognize it as a number. How do I make it into a date field? Bonus, I have 200703?

Answer 37

Calculated field. Examples assume the source is of type “string” (otherwise, wrap str() around these):DATE(MID([DividendDate],5,2) + “/” + RIGHT([DividendDate], 2) + “/” + LEFT([DividendDate],4))Bonus question:DATE(MID([DividendDate],5,2) + “/01/” + LEFT([DividendDate],4))

Question 38

Do you have a Modulus function in excel it is Mod()?

Answer 38

Yes we do have a modulus function. Instead of MOD(), Tableau has a modulo operator, in calculated fields this is the “%” character, e.g.: [Dividend] % INT[Divisor]The modulo operator finds the remainder when dividing by integers: 5%2 = 1

Question 39

Can I use calculations from Excel?

Answer 39

Yes. Once connected to the excel sheet, calculated fields are represented inside of Tableau. A change to the calculation in excel will result in updated information in the Tableau worksheet. Note: you cannot use VB or other excel functions inside of Tableau, only the output of those functions/calculations.

Question 40

Can I use functions in my database that Tableau doesn’t have?

Answer 40

Yes. You can use the various Tableau “RAWSQL” functions to return results from functions in the underlying database. Care must be taken to present the results of these functions in meaningful ways (i.e. a db function that returns a list might need to be ‘counted’ in tableau) (stored procedures may also work, as long as they return a single value for each row – aka single-valued functions)

Question 41

I have financial data. How do I calculate the profit ratio?

Answer 41

Tricky question. Their table will look like this:Account AmountGross profit 100Expenses 1049Revenue 2302COGS 8239Etc.Best way is a couple of calculated fields, e.g Profit: IF [Account] = “Gross Profit” THEN [Amount] ELSE NULL ENDRevenue: IF [Account] = “Revenue” THEN [Amount] ELSE NULL ENDEtc, then, perform your math on these calc fields: sum(Profit)/sum(Revenue)

Question 42

How do I calculate year-over-year growth without using table calcs?

Answer 42

Hint: similar to the ‘financial data’ question above.

Question 43

How do I sort on a particular dimensions’ member (i.e. sort on 2008 of “order date”)

Answer 43

I.E Do this, but without single-click sort:

Question 44

Can Tableau calculate the result of compounding interest?

Answer 44

Yes. Tableau can certainly do this with some table calculation functions:PREVIOUS_VALUE(1)*(1+AVG([Discount]))

Question 45

Can Tableau do z-tests?

Answer 45

Yes, the calculation is: (Sum(x) – WINDOW_AVG(Sum(x))) / STDEVP(x) where x is a field.Z represents the number of standard deviations between x and its population average. As a general benchmark, any Z scores above 2 are considered statistically significant.

Question 46

The Tableau Server maintenance views do not fit my needs. Can I customize them?

Answer 46

Yes. See the admin guide or knowledge base for details.

Question 47

How can I embed a view without the toolbar?

Answer 47

Add to the end of the url: ?:embed=yes&?:toolbar=no

Question 48

Can I do a multiple regression?

Answer 48

No. But Tableau does iterative regressions with ANOVA very well.

Question 49

What does the Anova table tell me?

Answer 49

analysis of variance: 1. Sources of variation (AKA “Field”) 2. Degrees of freedom 3. Sums of squares 4. Mean squares 5. F-statistic 6. P-Valuefor further information, see also: the internet.

Question 50

What is a p-Value?

Answer 50

A p-value shows the probability that sample data will adequately represent the population from which it was drawn. The larger the value, the less likely that the sample data accurately represents the population. The max. value for a P-Value is 1.0, min is zero, and a value of .05 means that there’s a 5% chance that the model does not represent the data.

Question 51

Can I use a cumulative distribution function?

Answer 51

Most likely. It depends on exactly what they are asking, but start with a histogram and set the measure to be ‘running total’. If they like that, then set it to percent of total as well (secondary table calc).

Question 52

Can I find the correlation?

Answer 52

Yes – compares two sets of data against each other and contains two separate sets of measures.

Question 53

Can I do significance testing?

Answer 53

Significance is built-in to the ‘describe trend model’ command. A possible advanced approach is to calculate your significance manually, however this requires a detailed understanding of your dataset and what significance you are testing for.