Tableau Study Set 2
At what level are dates automatically aggregated?
year
How can you find your cost if sales+ profit exists, but the cost pill does not?
(sum)sales - (sum)profit
(explanation starts at 43:29, I think this is from Sam video "Tableau with Sam Table Calcs Partition, Jan 7...): How do you figure out average number of calls a day, so you can compare days with commercials to days without commercials?
* Start by making a new calculation, call it Average number of calls. It's calculation string will be: Window_avg (Sum(number of calls)) --as a reminder, any time you are doing a table calculation you have to aggregate within it as you did above -Now, go to that calculated field under measures and make it discrete, so that when you drag it onto the sheet you will see it as text, instead of a viz -Now, go to the pill for Average number of calls and go into the advanced setting, so that the avg is confined to the subset of whether the days had commercials or not. "Addressing" will be Day of new date time since that's where you will partition, and "partitioning" will be commercials or not since that's what you will use to put the barrier up - you are partitioning with Commercials or Not
How can you fix an incorrect sort when you need to limit a sort to a certain area?**
-create calculated field index () -make sure you convert to discrete -index will perform a rank function. Put the pill were you would want it to appear in the view of the table -right click on index and table calculation, then advanced -bring both fields to the addressing side, arranging them once again in the order seen in the view, then choose measure used to rank
How to create parameter that returns one measure for one dimension, and a different measure for another dimension***
-create parameter with string+sales+profit -create calc to tie it to he viz -case [sales or profit] -When "profit" THEN [Profit] -When "sales" THEN [sales] END
When can you not do a forecast?
-cube -on a view with table calcs -view with totals, subtotals, percent calcs
If you think have have multiple records for one unique identifier (such as multiple records for one physician's unique ID, or multiple ways of writing a company name for one registration number) how can you get Tableau to show you which identifier numbers have more than one record associated with them?
1) Drag the identifier to the filter 2) Go to the Condition tab 3) Select By field, Number of records, Sum, >, 1
How do I calculate the average number of calls for a) days with commercials and b) days without commercials
1. First click Create Calculated Field (name it average number of calls) 2. Start with WINDOW_AVG 3. Do sum (# of calls)within the WINDOW_AVG calc 4. Convert this calculate field pill to discrete to ensure the number shows up as text 5. Click on average number of calls and click advanced and you see partitioning and addresses -address the calls and partition by whether days have commercials? This is at 43:30 with Sam I think
When to use an extract
1. provide a local copy of the data to make the workbook "portable" 2. Limit access to the source database (for security, administrative, or load reasons) 3. Provide performance improvements over direct database access
When not t ouse an extract
1. you want to control where the data resides 2. you want all queries run against the source system for the freshest data
How do you pull measure values over as multiple lines on the same graph?
1:39:25 from Brian Tableau Synching Google Pull them to the left side of axis until you see two little bars next to each other
What is a flat file?
A data file with no structured hierarchy
What's an integer?
A whole number
What does R square mean?
A higher number means the line fits the data almost perfectly - so larger is good though a really high number like .99 can indicate a problem
At what level are blends done?
A sheet by sheet basis with Tableau workbook
What P value indicates a significance in the trend?
A significant (credible) trend is usually considered to be "less" than .05 (so larger numbers indicate it may just be chance)
What is a parameter?
A variability in the equation whose value can be controlled by the end user
When you are looking at a measure in Tableau, how can you make it more granular?
Add a dimension. You can do this by dragging a dimension to the column/row OR dragging it to the marks card
If you want more marks on a view, what should you do?
Add dimensions
In forecast, what is the default setting for trend and season?
Additive
If you want Tableau to fill in a lack of data with zeros (such as when looking looking at number of calls throughout a day) how do you do that? what's the advantage?
Advantage is a smooth, continuous line To do it: Click on the pill to edit in shelf; type zn, then type an open parentheses at the beginning of the line, and close parentheses at the end of the line
What's one way to describe continuous values?
All the shades between the discrete numbers or "buckets"
What happens when you do a blend, and miss a link?
Among other things, it can create duplicate records because they're not properly merged
What is an example of a data source that cannot be extracted?
An OLAP database
What does an asterick mean when data blending?
An asterick means there is more than one value for a field cell
What does a continuous pill create when brought into a view?
An axis
To do Top N products sold across each region, what will you use?**
An index calculation; it essentially removes the "and" Tableau uses the rank instead: type INDEX() -then convert the pill you just created to data type discrete -put this new pill between the category you want to address, and the dimension you want to address it by. -for example: Region, Rank, Product ID -click on the table calc. for this new index calc field -under compute using, select advanced -bring both pill (in this case, region and product ID) over to the addressing box -Make sure the field you want to bucket the ranking by is at the top -on calc. defining for "at the level" click what you want the rank to be assigned by (if it was top 10 menu items by sales, you would click menu items here) -for "restarting every" click on where you want to recalculate the rank; so, if you want to recalculate by every restaurant to get the top 10 menu items by restaurant, you would click restaurant here -bring a quick filter over for the bucket you would like to look at, control click to bring "rank" over to filers and click 1-10
What does data blending require?
At least one common field across both data sources
How many seasons and data points are needed for a forecast?
At least two seasons, or at least five data points if it's not seasonal
What color are discrete pills?
Blue
If you want to filter more than one value for a dimension, how?
Bring the pill dimension to the filter tab, go to the condition tab, and go to "by formula"
Are text dimensions continuous or discrete?
By definition it has to be discrete
What calculation would tell you if a parameter name is in another string?
CONTAINS(string to search in),(parameter string)
What's a way to look at COUNTD in comparison to sum?
COUNTD is kind of look sum for dimensions, counting the number of times an item occurs;
What are dimensions?
Categorical fields; the fields we want to slice and dice our data by
If you want to see the details of what the data interprter did, what should you do?
Click "review results" - headers will be in red, data will be in green
How do you copy a pill?
Click control and drag (shift will make it do something else)
How do you change the range of values on your axis?
Click edit axis (right click?)
How do you "turn on" labels?
Click on "label" on the marks card, and then click "show mark labels"
How can you add quick filters to a dashboard?
Click on a carrot on any sheet that uses those dimensions and use the filter
If you want to filter your data source as the data is brought in, how do you do that?
Click on add in the upper right hand corner to add filters
How do you export data to a spreadsheet, particularly when working with a table and this is not obvious?
Click on analysis, view data, then export all; It will be a csv file so you will separate on the commas when importing into the excel sheet
how to add reference line to the view?
Click on analyst tab, pull reference line to table
If you only want to bring some elements of the data into your extract, how do you do that?
Click on edit, to the right of the extract
To show labels, what do you do?
Click on labels, or the ABC button up top
How do you change shapes?
Click on shapes tile, then more shapes
On the above, how do you remove the filter?
Click on the data source, then "edit data source filters"
If you want to sort ascending or descending by a measure not on a column or row (like one in the marks card) how do you do that?
Click on the dimension, and then on the appropriate sort button on the top in the tool bar
How can you add a dynamic title in the dashboard?
Click on title to be in "edit title" menu, it's an option there
if you want a reference line for a value you pick, what should you do?
Click the analytics pane, then constant line
What is the difference between count and count distinct?
Count distinct only count distinct values; so if a value was repeated multiple times it would only count once
If you are trying to make a pie chart, and it won't give you the option or it shows up as a bunch of different pie charts, what do you do?
Create a new sheet. Move a measure pill to the column shelf, and a dimension field to the rows shelf. From there it should give you the pie chart option.
what's one resource for Tableau training?
Datablick.com
What can custom SQL do for you?
Define the data set: determine what data is brought into TAbleau
What is the first level of certification
Desktop qualified associate
What defines the number of marks, and granularity of the view?
Dimensions define the number of marks, and granularity of the view
What are the Google Analytics measures and dimensions that worked together without an error (most recent in tableau)
Dimensions: Date Hour Keyword Lat Long Referral Path Source/Medium Measures: Bounces Organic Searches Session Duration SEssions
Any dimensions you drag onto the cards or shelves change
the level of detail
What are the Google Analytics measures and dimensions that worked together without an error?
Dimensions: Ad content, ad group, date, hour of day, keyword match type, source, measure names measures: adsense ads clicked; clicks, organic seraches, number of records, measure values
How can you ensure a left outer join is happening?
Drag both to rows, then see that there are nulls in the column for the second filed to show it did not match with the first field.
How can you keep a calculation you added to the shelf?
Drag it over to the data pane
How do you create a hierarchy?
Drag the subordinate field onto the primary field
If in an IF/THEN statement, what is the word always before END?
ELSE
What things don't change LOD?
Filters, pages, tooltip
To see both the number of radio commercials and number of calls on the same graph, how do you ensure the axis step-ups in volume for both?
First click on dual axis by right clicking on pill; right click on axis and click synchronize axis
How do you create a context filter?***
First, click on filter desired in the filter shelf; then right click "add to context"
Whats the fastest way to clear sorts?
Go to clear sheets, then clear sorts
How do you create groups?
Go to the dimension within which you want groups; right click to create group; click on the things you want; click on group; name group; note that field name is the overall category
How do you make your viz fill the whole screen?
Go to the tool bar at the top, under "help" and click on drop down menu to fit to width
If you want to disaggregate data on a scatter plot, what should you do?
Go under analysis and un-check aggregate data
What color are continuous fields?
Green
What color are continuous pills?
Green
What's the difference between groups and sets?
Groups are metadata definitions. Groups can't be combined or used in calculated fields. Sets are a combination of data.
How can you change the format to put all the data in the column for that category?
Highlight on columns to combine, scroll to right, click on arrow and click "pivot"
If you want to group marks on a viz by dimension, what do you do?
Highlight the marks, click on group, and then group by dimension and all the marks with that dimension will show up
Include breaks the calculation down according to ---?
How the view is structured
How do you tell Tableau to average corporate sales but return the median of other sales
IF ATTR ([segment])='corporate' THEN AVG (sales) ELSE MEDIAN (SALES) END
What is the calculation for getting the average of corporate sales but the median of other sales?
IF ATTR (segment) "corporate" then AVG (sales) ELSE Median (sales) END
Calc to tell Tableau to just return sales number for the product category the user selects?
IF [category] = [parameter for category you create] THEN [sales] else END
What's an easy calculation that defines positive profits
If >=0 then "positive" else "negative"
What's the version of the formula above that should be used if some of the numbers don't have a zero at the beginning, and you want them to?
If LEN([HOUR])=8 then [Hour] else '0' + [Hour] END
What's the advantage of creating a set with parameters?
If you bring the set over to color you can dynamically change what's "in" the set by changing the parameter
If you have a cube, is there anything you should consider when connecting?
If you have the original relational database, try building extracts from it in Tableau
With a cube, what should you know about aggregation?
In a cube, aggregation is part of the data type, and cannot be switched within Tableau
In a level of detail expression, which words should be at the beginning of a calculation?
Inside the { symbol, the choices are Include, Fixed, or Exclude
What is the difference between a blend and a join?
Instead of data being blended at the row level, is is queried to the data source and aggregated
What does the partitioning field do?
It breaks up the field being addressed
What can an aggregation in an LOD calculation NOT contain?
It cannot include a table calculation or the aggregation ATTR
How is a parameter helpful?
It creates a number you can allow the end user to change in the calculation (for instance, if parameter were SKUs sold and they wanted to see everything over a certain number)
What does a logic calculation do? What's another word for it?
It determines if a certain condition is true or false; it is also known and boolean logic Example: IF (Attribute(Market)="Africa" AND SUM(SALES) >(parameter) THEN "TRUE" ELSEIF .... ELSE "FALSE" END
In a union, what happens when two tables have the same record?
It goes on there twice
How can you quickly identify the primary data source in Tableau?
It has a blue check mark.
What does this calculation do?: MAKEDATETIME [DATE], MAKETIME (int,(hour),0,0)
It is showing a time inside a certain day
What does the "Fixed" word tell Tableau?
It only looks at that dimension, not breaking it down by how the view is structured
What does attribute do?
It returns the value if there's one value, otherwise it returns and asterick; essentially, it tells Tableau the work to look for and then what logic to apply
What does the colon do in a level of detail expression?
It tells Tableau we're done declaring the level of detail, and are about to type the aggregate expression
What does the term "ELSE" do in an "if" calculation?
It tells Tableau what to do with a value if none of the previous conditions were true
What does the ZN function do?
It tests to see if a field value is "null." If it is, it returns a value of zero. in other words, in this calculation, Z (zero) equals n (null)
How do you know a data source is an extract?
It will appear as two cylinders with an arrow
How can you tell if a data source is the primary connection being used with the viz?
It will have a blue check
How can you make a filter apply to all on a dashboard, in terms of controling the rest of the dashboard?
It's a menu option under carrots
In cube, what should you know about continuous data?
It's always at the lowest level of detail, and cannot be rolled up to monthly or yearly view
What's a context filter?
It's computed first, and then all other filters are computed on its results
What is an "addressing" field?
It's the field Tableau includes in the calculation to determine each result
What is the best way of bringing a pill over, other than a drag and drop?
Just start typing its name in the shelf
What are the rules for which dimensions cannot be used in the calculation?
LOD declaration CAN use multiple dimensions, but they must be from the same data source and you cannot combine fields
If you are doing a table calc and it doesn't look right, what's a good idea?
Look at the table calc, then summary and check the 'partioning' and 'addressing' fields
How do you turn a double digit number into a single digit number that eliminates the zero a the front?
MAKETIME (INT[hour]),0,0 So for instance 08 becomes 8:00:00
How do you turn a time like 06:14:22 into one that has zeroes for minutes and seconds?
MAKETIME (int(left[HOUR],2),0,0))
What language does cube use?
MDX -cube is the same thing as OLAP
When doing Top N and rankings, what's something to be careful of in terms of sorting?
Make sure you correctly choose ascending or descending - for top 10, descending
What are two examples of cubes (OLAPS)?
Microsoft analysis services, oracle s base; they process and transform data from a relational database into preaggregated results with defined hierarchical strucures for standardization and quick access to results
Can you make extracts from cubes?
No
If you modify a filter on story filter, does it change anything on a dashboard or sheet?
No
If your data is split up into panes in the view, do you need to do an advanced calc in Tableau for running total look at each year, by itself?
No - if you select the opption to have the running total aggregate by pane, it will automatically confine itself to the pane without having to be told where to restart the view
When you do a data blend, are all the fields included?
No, just those in the primary data source; it's a simulated left join
What is the P Value?
Number assigned to the concept of significance
What does OLAP stand for?
Online Analytical Processing
If you want to label the viz with the rank by profit, what's the calc?
RANK(SUM9Profit), 'desc')
What's the best option for viewing if it will be viewed on a lot of platforms?
Range
If you decide while working with data that you want to create an extract, what do you do?
Right click
If you want a field to be used as both part of a hierarchy and by itself, how do you do that?
Right click and click duplicate
How do you create a calculated field table calculation?
Right click for calc, go to table calc, click what you want
If you want to divide a measure into equal "buckets" (such as each $1000 of sales) what do you do?
Right click on bins
How can you share a datasource with others?
Right click on data source and use "publish to server"
How do you create a data source filter?
Right click on data source filter and click edit data source filters
How do you refresh an extract?
Right click on data source, go to extract, refresh extract
How can you pull up the full information about the trend line?
Right click on describe trend model; it pulls up the full model summary and Nova table
How do you create a calculated set? ***
Right click on dimension on
How can you replace a data source?
Right click on it and click replace data source
What's the easiest way to sort in a pill in a row or a shelf?
Right click on it, then click sort and then fine tune from there; you can click on a field not in the view if you like
If you want to save a data source for future use, what should you do?
Right click on the data source, and click on "add to saved data sources" it will save as .tds, which just tells Tableau where to find that data; it will be saved in the "datasources" in My Tableau Repository
If you want to see all fields other than your group for comparison's sake, what do you do?
Right click on the group and click "include other"
For top N, how do you find top N across all data sets?
Right click on the pill on the filter shelf, go to the "top N" tab
How do you replace a data source with another?
Right click on what you want top replace. Click "edit data source"; click on data; click on edit connection
If you need to modify the data connection, what should you do?
Riight click on the data source, options appear from there
For the quick filter, if you only want to show values relevant to what's clicked in the above quick filter, how do you do that?**
Select only relevant values
How can you quickly clear a sheet in Tableau?
Shift, Alt, Backspace at the same time
What an example of a data source that must be extracted?
Some cloud-based databases "must" be extracted
What is the residual?
Space between a data where and where the model predicted it would be - also known as the error
What is an advantage of LOD over table calcs, in terms of what can be ignored?
Table calcs can never ignore something in the view whereas LOD calcs can
What do quotes do in a Tableau calculation?
Tells Tableau to treat it as a word
If a database connector for your database does not exist in Tableau, what can you use?
The ODBC connector
What is the difference between a band and a line?
The band calculates the difference between two lines
If you put a discrete field on color what happens, as opposed to if its continuous?
The discrete measure creates contrasting colors, whereas a continuous pill creates shades of the color
In Top N, what's really important to remember about filters?
The field you are bucketing by has to be brought to the filter shelf "before" you bring rank to that shelf otherwise it will not filter on just your bucket
What are measures?
The numbers we want to analyze
What what level does Tableau perform LOD calcs?
The row level
In a data blend, which is the primary data source?
The sheet you pull on to the viz first
In a cube, if you to group items in a category how do you do that?**** this shows up around 3:18 int he video**
There's no group; right click on the data pane, select calculated members in name box; type name in hierarchy
What should you know about heirarchies in Cubes?
They are pre-defined and cannot be changed in TAbleau
Are dimensions discrete, or continuous?
They can be both
What number of dimensions can be in groups and sets?
They can only be in one group, but can be in many sets simultaneously
When you create a calculated field for a workbook, how will that field show up on the other sheets when you're on data source?
They will show up as null. Don't try to delete or change them! This causes problems when multiple sheets have fields with similar names.
What does true mean in the above?
True means it is a part of the group
True or False?: The rows, the columns, and the marks card all affect the level of detail within the view
True. The marks card also affects the level of detail you are seeing.
What is the solution to an asterick?
Try switching which source is primary; of course, you can also do a real join
How can you go back to the live connection?
Uncheck the "use extract" option
What's the best way to group on a measure that will change as data is refreshed?
Use a calculation. For example: If sum(profit) >100,000 THEN "true" ELSE "false" END
If you want to determine time between two times/dates, what do you do?***
Use calculated field: DATEDIFF('day',(order date)
When will the hide sheet option be available?
When it's in use on a dashboard or story
When is the best circumstance in which a live connection is warranted?
When we want to use rapidly changing data, or a high performance database
When should you use INCLUDE?
When you want to also include other dimensions in the view when aggregating, such as wehn you want the average by Order ID by country. Country wasn't in the calculation, but it is in the view
How can you strip decimals from a number?
Wrap the whole thing in the integer function
When doing a union, do columns have to have the same name?
Yes - if they don't, Tableau will consider them separate columns. To make them one column after you've done the union, highlight them and click merge
Does data from the second source when doing a blend have to be aggregated?
Yes, and sum is the default aggregation
Does it matter what order days, hours, etc are in the columns field?
Yes, the first one is the primary and how Tableau will start to take them down.
What does include mean?
You are using the dimension, and also bringing in information from any other dimension seen in the view
Can you change filters in the Tableau reader?
You can click and unclick but the workbook cannot be edited
Where are all the places you can change a data type?
You can do it when looking at the sheet by right clicking, or do it in the grid or metadata view under the data source
What's a bolinger band?
usually used with stock to express if they are oversold
How can you determine if your trend line is truly helpful in predicting an outcome?****
You need to get the residual values -go to the worksheet,export data, save, check connect after export -put the driving factor on columns, residual on rows; bring dimension to level of detail -residual plot should be normal, distributed (scattered) around zero
Is a join horizontal or vertical, and why?
a join is horizontal, because it adds more columns
What kind of join is a blend doing?
a left join
What is another name for a table?
a sheet: a sheet in excel is the same as a table in a database
Fill in the blank: in calculating a blend, all fields involved must _______
all be aggregated or not
What is a table calculation?
an additional computation done on top of results set within Tableau
If you only want someone to be able to access a workbook if they already have access to the same file how should you send it?
as a non-packaged workbook
What color is the primary data source?
blue
What color are discrete fields?
blue; they create labels in the chart
How can you use parameters to figure out "top X"
bring over what you're ranking, tab over to top, and where it has the number like "10" change it to be "parameter" instead
How on the dashboard can you set it up so you see different lines, chosen by a drop down menu?
case[view by] when 1 then COUNTD ([Google Session]) when 2 then SUM 9[Call center Data-Master].[number of calls]) when 3 then sum (nov and dec. google ad].[clicks]) END (I think this starts around 1:37:15 with the video "Brian synching Google Ad Words)
If you want to connect to an extract when you open a new workbook, how do you do that?
click on "other files" on source home screen
How do you add a trend line to a sheet?****
click on analytics, then click trend line, the one you want, and drag it to the sheet
How do you make all the days show up, even if you don't have all the days in your data set?
click on days, go to the lower block for the continuous version and then move it onto the marks card for lines
If you are unsure of what to do to get started with new data, what could you do?
control click on measures and dimensions you want to analyze, and click show me
What symbol do you use on either side of level of detail expressions?
curly braces {}
If you need a profit ratio, what's the calc and why?
sum(profit)/sum(sales) Using the sum calc tells Tableau to aggregate to the level of the view; if you just did profit/sales it would compute at the lowest level of granularity and sum the ratios
What two things do you need for a forecast?
date and measure
What pills have full sorting options?
discrete dimensions
What pills have limited sorting options?
discrete measures
What would using COUNT do in the above?
double count (or more) ID values that show up more than once
How can you identify a calculated field?
equal sign before ABC, =ABC
The dimensions used to build a view determine how many ____ we have
marks
Can Tableau write changes back to the data source?
no
Can you read KPI data type from cubes?
no
If you wanted to look at yearly sales by category, how do you arrange the pills?
put "category" before the sum of sales
What does ZN do?
returns a zero when there's a null - just type ZN and then bracket it
How can you change the default aggregation?
right click on default properties, then click on aggregation for options
Once you replace a data source, what do you do if Tableau can't find the data for a field that was named different things?
right click on the old field, click replace references, and click on where the refreshed data is.
How do you make one trend line from multiple trend lines?
right click, click on trend lines, edit trend lines - then uncheck "allow a trend line per color"
If you want to see when you acquired a customer, how would you do that?
{FIXED (customer ID):MIN(order date)}
If you wanted to create a calc that showed how many order you had for each customer, how would you write that?
{FIXED [customer ID]:COUNTD[order ID]
