Calculated Fields

• Calculated fields allow new fields to be created from data that already exists in a data source.

Scenarios for Using Calculated Fields

• GDP, or Gross Domestic Product, is all of the money spent by consumers, businesses, and governments.
• Population represents total population.
• A new calculated field that determined GDP per capita can be created by calculating a ratio of GDP and Population using formula SUM([GDP])/SUM([Population]).

Calculated Fields Capabilities

• New fields can be created using existing ones
• String values can be formatted
• A field's data type can be changed to work with others
• Date fields can be manipulated
• How data is aggregated in a calculation can be controlled
• Calculated fields consist of: Fields, Functions, Operators, and Parameters.
• SUM([GDP]) / SUM([Population]) + [Parameter] calculates GDP per capita.

Creating Calculated Fields

• To create calculated fields, use the calculation editor.
• To open the editor, select Create Calculated Field from the Data pane or from the Analysis menu.
• Clicking Apply allows one to preview how the calculation changes the data in the view.
• Clicking OK saves the calculation.

Ad-hoc Calculations

• Type the operator, the first few letters of the function, and the first few letters of the field name into the calculation.
• Fields can be dragged into the parenthesis after the function.

String Functions

• LEFT(string, number) will show the leftmost number of characters within a string
• RIGHT(string, number) will show the rightmost number of characters within a string
• SPLIT(string, delimiter, token number) returns a substring from a string, using a delimiter to divide the string into substrings and then using the token to determine which substring to return.
• CONTAINS(string, substring) returns true if the given string contains the specified substring; otherwise returns false.
• REPLACE(string, substring, replacement) searches string for substring and replaces it with replacement, if substring is not found, the string is not changed.
• UPPER(string) updates the string to uppercase characters.
• LOWER(string) updates the string to lowercase characters.
• TRIM(string) returns the string with leading and trailing spaces removed.

Date Parts

• When using a date_part argument in a date function, note that it must be singular, all lowercase, and wrapped in single quotation marks, like: 'month', not 'months', 'Month', or 'MONTH'.

Date Functions

• DATEPART (date_part, date, [start_of_week]) returns part of a given date as a number.
• DATENAME (date_part, date, [start_of_week]) is similar to DATEPART, but it returns part of a given date as a string.
• DATEADD (date_part, increment, date) returns a date in which the specified increment has been added to the given date.
• DATEDIFF (date_part, start_date, end_date, [start_of_week]) returns the difference between the two dates using the specified date part.
• DATETRUNC (date_part, date, [start_of_week]) truncates or “rounds down” the given date to the level of the specified date_part and returns a new date.

Scenarios using Dates

• When working with product shipping data with order dates and ship dates, but without calculating the number of days between them, you can show the average number of days between the order and ship dates for product sub-categories, organized by product category with a date calculation and adjust the aggregation type.

Aggregate Functions

• SUM, AVG, and MEDIAN are common aggregate functions.

Mathematical Order of Operations

• The mathematical order of operations provides a convention for how mathematical expressions are interpreted and solved in the following order: Elements within parentheses, Exponents, Division and multiplication, Addition and subtraction.

Using Aggregate Functions

• Aggregate functions can be used in calculations such asSUM([Marketing Expenses])/SUM([Sales])

Usage Examples

• When a data contains fields for Profit and Sales of a company's products and the goal is to find the profit ratios for each year, broken down by Category, a Profit Ratio field can be created via a calculated field.

Level of Detail Expressions

• When a chart/graph is created in Tableau, its level of detail can be decided by adding categories ("dimensions") to Rows, Columns, or the "Marks card."
• More categories = more detailed chart.
• Level of detail (LOD) refers to the aggregation or granularity of the data.
• LOD expressions determine the levels of detail used in a calculation without actually dropping dimensions into the visualization.
• The level of detail at which a calculation should be performed independently of the level of detail can be defined.
• LOD uses keywords to perform calculations at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED).
• "INCLUDE" makes the calculation more detailed by considering more categories.
• "EXCLUDE" makes the calculation less detailed by ignoring some categories.
• "FIXED" lets one set a specific detail level that doesn't change, no matter what's in the chart.
• FIXED computes a value using the specified dimensions, without reference to the dimensions in the view.
• {FIXED [Region] : SUM([Sales])} expression computes the sum of sales per region.
• FIXED calculation can be used to show the average profit by product and create a dual axis chart that compares these values to average sales.

Table Calculations

• Table calculations are calculated fields applied to the values of a measure in a visualization.
• They compute the local data to transform the values based on what is currently in the view.
• Can be used for: Transforming values to show rankings, running totals, and/or percent of total.
• Quick Table Calculation can be used to apply one of these calculations, click the drop-down menu on a measure field in your view, point to Quick Table Calculation, and then click the desired calculation type.

Quick Table Calculations

• Running Total - Adds totals across the table.
• Difference - Calculates the difference from the previous column, across a table.
• Percent Difference - Calculates the percent difference from the previous column, across a table.
• Percent of Total - Computes a value as a percentage of all values within the table structure, across a table.
• Rank - Calculates the integer rank of the value across the table.
• Percentile - Calculates the statistical percentile of the value across the table.
• Moving Average - Calculates the average value based on a range around the current value, smoothing short-term fluctuations.
• YTD Total - Year-to-date total calculates the running total from the beginning of the year across the table.
• YTD Growth - Year-to-date growth calculates a percentage change from the same time period in the previous year and then calculates a running total over a year.
• Compound Growth Rate - Calculates the current value as a percentage from the first value.
• Year Over Year Growth - Calculates a percentage for the current value compared to the same period in the previous year.
• EX: creating a bar chart by calculating the percentage of annual change in sales of tables using a quick table calculation to explore these fluctuations.