Tableau

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

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"

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

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 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

What's an integer?

A whole number

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

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

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 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

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

What is an "addressing" field?

It's the field Tableau includes in the calculation to determine each result

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 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

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

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

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

How can you identify a calculated field?

equal sign before ABC, =ABC

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

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 {}

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

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 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

Any dimensions you drag onto the cards or shelves change

the level of detail

What's a bolinger band?

usually used with stock to express if they are oversold

At what level are dates automatically aggregated?

year

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]