Gold Cert Flashcards
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?
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!!
How does unchecking ‘AnalysisAggregate Measures’ differ from converting a measure to a dimension?
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.
What is an ‘Attribute’ (ATTR)? How does it differ from dimensions and measures?
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
Can I connect to a stored procedure?
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.
Does Tableau have any API’s or scripting?
“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.
Your website mentions the VizQL language. How do I program in this or modify the VizQL?
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.
How do I group thousands of items together?
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…
Can I save a filter for reuse? For example I manage 12 of the 5000 products we have.
Yes. Create the filter, and then choose “Create Set” from the filter’s context menu
If I have a calculated field that includes members from two blended data sources, will the results be included when I create an extract?
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.
How do I move a sheet from one Tableau workbook to another?
Bookmarks. Save one from the source workbook. Open the destination book, choose that bookmark, save the file. (Note: you cannot bookmark a dashboard)
Can I move dashboards from one workbook to another?
No.
What is the polygon mark type and how do I use it?
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.
What are the two main types of Joins?
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.
I created a join and most of my data is missing. How do I fix this?
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
What is Join Culling?
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.
How do I do Sparklines?
Create a line chart.Edit the axis to not include zero.Hide the axisMake the rows really tiny
How do I create a chart the shows the budget compared to actual?
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.
How do I show the rank of my products?
- Create a calculated field: index()2. Add this to your sheet and change it to discrete.
Can I create a chart that shows the trailing 12 months revenue?
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.
Can I create Control Charts?
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.
Can I do a Pareto Analysis?
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.
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:
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.
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?
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.
Setup a view that displays only a list customers whom purchased from BOTH Furniture and Office Supplies in the past.
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
Can I use Tableau to discover which products were frequently purchased together?
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).
How about the top 5 products that I sold this year?
Yes. Filter on current year, then add this filter to the “context”. Reason: context is created prior to filtering to the top 5.
How about the top 5 in each region?
Yes. Create an index() calculation and add it to the sheet. Edit it to restart for each region.
What about how the top 10 products and regions are performing?
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:
How do I make a box plot?
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.
Can I find the purchase history of customers that purchased this month?
- Manually filtering2. Dynamic sets from dimensions3. Manual sets from marks/members4. See also: cohort analysis.
Every day, I want to open Tableau and see how yesterday’s top 3 sales people have performed over the last 6 months.
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”.
How do I apply weights when working with my survey data?
If you have a weight field in your data set you can apply this weight with a simple calculated field. [Weight]*[Measure].
How do I find the average sale per order?
Calculated field. Given a dimension “Order” and a measure “line item sale amount”Sum([line item sale amount])/Countd(Order)
Tableau only created equal sized bins, but I want mine to be 0-10, 10-50, 50-100, 100-1000, and greater than 1000.
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
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?
- 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’)
Is there any way that we can dynamically change the size of our bins?
Yes, Tableau makes this very easy to do by allowing you to create parameters directly in the bin menu.
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?
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))
Do you have a Modulus function in excel it is Mod()?
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
Can I use calculations from Excel?
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.
Can I use functions in my database that Tableau doesn’t have?
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)
I have financial data. How do I calculate the profit ratio?
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)
How do I calculate year-over-year growth without using table calcs?
Hint: similar to the ‘financial data’ question above.
