Tableau: Calculated Fields

Calculated Fields in Tableau

• Calculated fields are used in various scenarios like calculating GDP per capita.
• The GDP per capita calculation involves dividing the sum of GDP by the sum of the population: SUM([GDP])/SUM([Population]).
• Calculated fields enable creation of new fields from existing ones, like deriving net cost from Sales and Profit fields.
• String values can be formatted using calculated fields to clean data by trimming spaces, changing capitalization, or replacing inconsistent values.
• Calculations can change a field's data type to work with others, such as combining a string field (Customer Name) with an integer field (Customer ID).
• Manipulation of date fields is possible, allowing for extraction of specific parts of a date or calculation of time between two dates.
• It's possible to control how data is aggregated in a calculation, like calculating the ratio of total profits to total sales.
• A calculation includes fields, functions, operators, and parameters: SUM([GDP]) / SUM([Population]) + [Parameter].
• Fields in calculations are existing data fields and any previously built calculated fields, enclosed in square brackets and shown in orange (e.g., [GDP], [Population]).
• Functions are operations performed on data depending on its type, such as aggregation functions (AVG, SUM, COUNT). Functions are blue and followed by parentheses.
• Operators are shortcuts for complex functions (+, -, *, /, %, ==, >, <). They are dark gray. The first operator is /.
• Parameters are placeholders for constant values, shown in purple and enclosed in square brackets.
• Comments are annotations for calculations, displayed in light gray and starting with two forward slashes (//).
• Clicking Apply allows you to preview how the calculation changes the data in the view, while clicking OK saves the calculation.
• You can create a simple profit visualization by placing Profit to Columns, and Market and Subcategory to Rows, and filtering Category to just Furniture.
• It is possible to have one distinct color for positive and negative numbers.
• Calculated fields can be created by defining a formula via Analysis > Create Calculated Field.
• The formula for positive/negative emphasis could be: IF SUM([Profit]) > 0 THEN "positive" ELSE "negative" END.
• Name the calculated field "sign of profit" and add a comment in code with //.
• An equal sign in front of the field indicates it’s a calculated field and not directly from the data source.

String Functions

• LEFT(string, number) and RIGHT(string, number) return the leftmost or rightmost characters.
• SPLIT(string, delimiter, token number) divides a string into substrings based on a delimiter.
• CONTAINS(string, substring) returns true if a string contains a specified substring.
• REPLACE(string, substring, replacement) finds a substring within a string and replaces it.
• UPPER(string) and LOWER(string) convert a string to uppercase or lowercase.
• TRIM(string) removes leading and trailing spaces.

Date Functions in Tableau

• When using a date_part argument in a date function, it must be singular, all lowercase, and wrapped in single quotation marks (ex: 'month').
• Relevant date functions:
• DATEPART(date_part, date, [start_of_week]): Returns the specified part of a given date as a number. For example, DATEPART('month', #January 23, 2021#) = 1.
• DATENAME(date_part, date, [start_of_week]): Similar to DATEPART but returns the date part as a string. For example, DATENAME('month', #January 23, 2021#) = January.
• DATEADD(date_part, increment, date): Returns a date with a specified increment added. For example, DATEADD('day', 7, #January 23, 2021#) = January 30, 2021.
• DATEDIFF(date_part, start_date, end_date, [start_of_week]): Returns the difference between two dates using the specified date part. For example, DATEDIFF('day', #January 23, 2021#, #February 24, 2021#) = 32.
• DATETRUNC(date_part, date, [start_of_week]): Truncates a date to the level of the specified date_part. This function returns a new date. DATETRUNC('month', #January 23, 2021#) returns January 1, 2021.
• To calculate the time it takes for an order to ship:
• Create a calculated field named "Time to Ship.".
• Use the DATEDIFF function: DATEDIFF('day', [Order Date], [Ship Date]).
• Ensure the calculation is valid before clicking OK.

Introduction to Table Calculations

• Table calculations are secondary calculations performed on a returned result set.
• Tableau has pre-defined, commonly used computations called Quick Table Calculations that include: Running Total, Percent of Total, and Year over Year Growth.
• Create a new sheet, put Category to Columns and Sales to Rows. Then move Order Date to Columns, expand to Quarters. Put Sales to Rows again
• You can select the Running Total Quick Table Calculation from the dropdown men.
• Can drag your Table Calc to the right axis for a dual axis chart then right click and synchronize the axis.
• Table calculations are indicated by a delta symbol in the pill.

Data Validation - Nulls to Zeroes

• Calculations can remove null values.
• Convert nulls to zeros to be able to generate a comparison.
• To convert nulls to zeros:
• Right-click in the data window and select create calculated field.
• Name it "Corrected Quota".
• Use the ZN( function: ZN([Quota])
• The ZN function returns zero wherever there is a null.
• Visualizations need to be fixed by right clicking in the map and viewing the data.
• Use SUM(Sales)-SUM(Corrected Quota) to view data in visualization.