Calculated Fields are additional fields that are a combination of formulas and existing fields in your data source. When you create a calculated field, you are essentially creating a new data field that can be used in your data visualisations.

There are three main types of Calculated Fields:

**Basic C**– Basic calculations allow you to transform values or members at the data source level of detail (a row-level calculation) or at the visualization level of detail (an aggregate calculation).alculations **Table Calculations**– Table calculations allow you to transform values at the level of detail of the visualization only. We will go through Table Calculations in-depth in TAB113.**Level of Detail (LOD) expressions**– Just like basic calculations, LOD calculations allow you to compute values at the data source level and the visualization level. However, LOD calculations give you even more control over the level of granularity you want to compute. We will go through LOD in-depth in TAB114.

For the remainder of this article, we will go through Basic Calculations.

*Note: We can only introduce you to Basic Calculations, but you will need to practice and get used to the common Calculations as well as build an awareness of what Calculations are at your disposal.*

## Calculated Fields

Let us get started straight away by creating a Calculated Field, you

- In a worksheet and Menu Bar, go to
**Analysis**and select**Create Calculated Field**. - Right-click in an empty space in the
**Data Pane**and select**Create Calculated Fields**. - Right-click on a Field in the
**Data Pane**, go to**Create**and select**Calculated Field**. - In the
**Data Pane,**on the right of the**Dimension**header, click on the Down Arrow (Down Carrot), and select**Create Calculated Field**. - You can type formulas directly in the
**Columns**,**Rows**and Marks and then drag the result onto the**Data Pane.**.

As you can see there are several different ways of creating a new Calculated Field depending on what is quickest at this moment. Please have a try and create Calculated Fields using all 5 methods.

*Note: If you know of an additional way of creating Calculated Fields, please do let me know. I am sure there are other ways too.*

## Calculation Editor

Once you go through one of

Let us have a look at what we have:

- Is where you put the name of your Calculated Field, this must be unique.
- This is where you will write your Calculated Field formula. This includes auto-complete as well as auto-formular-validation.
- This is a logical grouping of available Calculations. We will go through the formulas based on these groupings. You can also search to see if your formula you require exists.
- Here is the list of fomulas that you have available to you at present.
- If you click on a function listed in 4, you will see a short description.

All of this is available to you to use in your work. So let us go through what is available to us.

*Note: There is current no real way of defining corporate custom functions, this is a pet peeve, but I hope this will be a feature in the future.*

## LITERALS, OPERATORS AND SYNTAX

Your Calculations will be made up of a Formula that returns a value; your formula must be valid and can comprise of Data Fields, Calculated Fields, Operators and Functions. We are going to look through the list of functions available, but first:

**LITERALS**

**Comments**– are defined by two forward slashes:**//****Fields**– are defined by the field name within square brackets:**[Data Field Name]****Literals****String**literals are text between single or double quotes:**“Text”**,**‘Text’**.**Date**literals are date text between**#1 January 2019#**.**Boolean**literals are written as**TRUE**or**FALSE**. This is not case sensitive.**Numeric**literals are written as numbers which can contain decimals or who numbers.**Null**literals are written as**NULL**. This is not case sensitive.

**Parameters**– are defined in the same way as Fields.

**OPERATORS**

**+**is the**Addition**operator.**–**is the**Subtraction**operator.*****is the**Multiplication**operator.**/**is the**Division**operator.**%**is the**Modulo**operator.**^**is the**Power**operator.**=**or**==**is the**Equality**operator.**!=**or**<>**is the**Not Equal To**operator.**>**is the**Greater Than**operator.**>=**is the**Greater Than or Equal**tooperator .**<**is the**Less Than**operator.**<=**is the**Less Than or Equal**tooperator .

**OPERATOR PRECEDENCE**

Ideally you should always use parenthesis to make your formulars clear, but here is the Tableau Operator Precedence.

PRECEDENCE | OPERATOR |

1 | – (Negate) |

2 | ^ (Power) |

3 | * / % |

4 | + – |

5 | == > < >= <= != |

6 | NOT |

7 | AND |

8 | OR |

**NUMBER FUNCTIONS**

**ABS()**– Returns the absolute value of the given number.**ACOS()**– Returns the arc cosine of the given number. The result is in radians.**ASIN()**– Returns the arc sine of a given number. The result is in radians.**ATAN()**– Returns the arc tangent of a given number. The result is in radians.**ATAN2()**– Returns the arc tangent of two given numbers (x and y). The result is in radians.**CEILING()**– Rounds a number to the nearest integer of equal or greater value.**COS()**– Returns the cosine of an angle. Specify the angle in radians.**COT()**– Returns the cotangent of an angle. Specify the angle in radians.**DEGREES()**– Converts a given number in radians to degrees.**DIV()**– Returns the integer part of a division operation, in which integer1 is divided by integer2.**EXP()**– Returns e raised to the power of the given number.**FLOOR()**– Rounds a number to the nearest integer of equal or lesser value.**HEXBINX()**– Maps an x, y coordinate to the x-coordinate of the nearest hexagonal bin. The bins have side length 1, so the inputs may need to be scaled appropriately.**HEXBINY()**– Maps an x, y coordinate to the y-coordinate of the nearest hexagonal bin. The bins have side length 1, so the inputs may need to be scaled appropriately.**LN()**– Returns the natural logarithm of a number.Returns `Null`

if number is less than or equal to 0.**LOG()**– Returns the logarithm of a number for the given base. If the base value is omitted, base 10 is used.**MAX()**– Returns the maximum of the two arguments, which must be of the same type. Returns`Null`

if either argument is`Null`

.`MAX`

can also be applied to a single field in an aggregate calculation.**MIN()**– Returns the minimum of the two arguments, which must be of the same type.Returns `Null`

if either argument is`Null`

.`MIN`

can also be applied to a single field in an aggregate calculation.**PI()**– Returns the numeric constant pi: 3.14159.**POWER()**– Raises the number to the specified power.**RADIANS()**– Converts the given number from degrees to radians.**ROUND()**– Rounds numbers to a specified number of digits. The`decimals`

argument specifies how many decimal points of precision to include in the final result.If `decimals`

is omitted,`number`

is rounded to the nearest integer.**SIGN()**– Returns the sign of a number: The possible return values are -1 if the number is negative, 0 if the number is zero, or 1 if the number is positive.**SIN()**– Returns the sine of an angle. Specify the angle in radians.**SQRT()**– Returns the square root of a number.**SQUARE()**– Returns the square of a number.**TAN()**– Returns the tangent of an angle. Specify the angle in radians.**ZN()**– Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values.

Take a moment and experiment with the various number functions. I personally use **SIN()**, **COS()** and **RADIANS()** frequently for drawing. **ZN()** is also fantastic for dealing with null values. **MIN()** and **MAX()** and others on the list are commonplace in Tableau.

**STRING FUNCTIONS**

**ASCII**– Returns the ASCII code for the first characterof `string`

. **CHAR()**– Returns the character encoded by the ASCIIcode `number`

. **CONTAINS()**– Returns true if the given string contains the specified substring.– Returns true if the given string ends with the specified substring. Trailing white spaces are ignored.ENDSWITH ()**FIND()**– Returns the index positionof `substring`

in `string`

, or 0 ifthe `substring`

isn’t found. If the optionalargument `start`

is added, the function ignores any instancesof `substring`

that appear before the indexposition `start`

. The first character in the string is position 1.– Returns the position of the nth occurrence ofFINDNTH ()substring within the specified string, where n is defined by the occurrence argument.**LEFT()**– Returns the left-most number of characters in the string.**LEN()**– Returns the length of the string.**LOWER()**–Returns `string`

, with all characters lowercase.**LTRIM()**– Returns the string with any leading spaces removed.**MAX()**– Returns the maximumof `a`

and `b`

(which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings,`MAX`

finds the value that is highest in the sort sequence defined by the database for that column. Itreturns `Null`

if either argumentis `Null`

. **MID()**– Returns the string starting at index position`start`

. The first character in the string is position 1. If the optionalargument `length`

is added, the returned string includes only that number of characters.**MIN()**– Returns the minimumof `a`

and `b`

(which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings,`MIN`

finds the value that is lowest in the sort sequence. Itreturns `Null`

if either argumentis `Null`

. **REPLACE()**–Searches `string`

for `substring`

and replaces itwith `replacement`

. If `substring`

is not found, the string is not changed.**RIGHT()**– Returns the right-most number of characters in`string`

.**RTRIM()**–Returns `string`

with any trailing spaces removed.**SPACE()**– Returns a string that is composed of thespecified `number`

of repeated spaces.**SPLIT()**– Returns a substring from a string, using a delimiter character to divide the string into a sequence of tokens.**STARTSWITH()**– Returns true if`string`

starts with`substring`

. Leadingwhite spaces are ignored.**TRIM()**– Returns the string with leading and trailing spaces removed.**UPPER()**– Returns string, with all characters uppercase.

Have a look at these functions and try them out. I personally use **SPLIT()**, **LEN()**, **TRIM()** and surprisingly **CHAR() **very often. Explore and Experiment.

**DATE FUNCTIONS**

Before we go into dates, let us first look at a table of date parts.

DATE_PART | VALUES |

‘year’ | Four-digit year |

‘quarter’ | 1-4 |

‘month’ | 1-12 or “January”, “February” etc |

‘dayofyear’ | Day of the year |

‘day’ | 1-31 |

‘weekday’ | 1-7 or “Sunday”, “Monday” etc |

‘week’ | 1-52 |

‘hour’ | 0-23 |

‘minute’ | 0-59 |

‘second’ | 0-60 |

‘iso-year’ | Four-digit ISO 8601 year |

‘iso-quarter’ | 1-4 |

‘iso-week’ | 1-52, start of the week is always Monday |

‘iso-weekday’ | 1-7, start of the week is always Monday |

Now let us look at the functions available to us.

**DATEADD()**– Returns the specified date with the specifiednumber `interval`

added to thespecified `date_part`

of that date.**DATEDIFF()**– Returns the differencebetween `date1`

and `date2`

expressed in unitsof `date_part`

. **DATENAME()**–Returns `date_part`

of `date`

as a string.The `start_of_week`

parameter , which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are ‘monday ‘, ‘tuesday ‘, etc.**DATEPART()**– Returns`date_part`

of`date`

as an integer.**DATETRUNC()**– Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month.**DAY()**– Returns the day of the given date as an integer.**ISDATE()**– Returns true if a given string is a valid date.**MAKEDATE()**– Returns a date value constructed from the specified year, month, and date.**MAKEDATETIME()**– Returns adatetime that combinesa date and a time. The date can be a date,datetime , or a string type. The time must be adatetime .**MAKETIME()**– Returns a date value constructed from the specified hour, minute, and second.**MAX()**– Usually applied to numbers but also works on dates. Returns the maximumof `a`

and `b`

(`a`

and `b`

must be of the same type).Returns `Null`

if either argumentis `Null`

. **MIN()**– Usually applied to numbers but also works on dates. Returns the minimumof `a`

and `b`

(`a`

and `b`

must be of the same type).Returns `Null`

if either argumentis `Null`

. **MONTH()**– Returns the month of the given date as an integer.**NOW()**– Returns the current date and time.**TODAY()**– Returns the current date.**YEAR()**– Returns the year of the given date as an integer.

I do not think there is a single function I do not use with regularity. One of the key things and reference points is the date parts in the section above.

**CONVERSTION FUNCTIONS**

**DATE()**– Returns a date given a number, string, or date expression.**DATETIME()**– Returns a datetime given a number, string, or date expression.**DATEPARSE()**– Converts a string to a datetime in the specified format. Support for some locale-specific formats is determined by the computer’s system settings.**FLOAT()**– Casts its argument as a floating point number.**INT()**– Casts its argument as an integer. For expressions, this function truncates results to the closest integer toward zero.**STR()**– Casts its argument as a string.

You really need to know all of the above and changing data types in formulas is a must.

**LOGICAL FUNCTIONS**

**AND**– Performsa logical conjunction on two expressions.**CASE**,**WHEN, THEN, ELSE, END**– Performs logical tests and returns appropriate values. The CASE functionevaluates `expression`

, compares it to a sequence of values, `value1`

, `value2`

, etc., and returns a result. When a value thatmatches `expression`

is encountered, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned.**IF**,**THEN**,**ELSEIF**,**ELSE**,**END**– Tests a series of expressions returning the <then> value for the first true <expr>.**IFNULL()**– Returns <expr1> if it is not null, otherwise returns <expr2>.**IIF()**– Checks whether a condition is met, and returns one value if TRUE, another value if FALSE, and an optional third value or NULL if unknown.**ISDATE()**– Returns true if a given string is a valid date.**ISNULL()**– Returns true if the expression does not contain valid data (Null).**NOT**– Performs logical negation on an expression.**OR**– Performs a logical disjunction on two expressions.**ZN()**– Returns <expression> if it is not null, otherwise returns zero.

This is also essential viewing and something that you must have a play with and understand.

**AGGREGATE FUNCTIONS**

**ATTR()**– Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored.**AVG()**– Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.**COLLECT()**– An aggregate calculation that combines the values in the argument field. Null values are ignored.**CORR()**– Returns the Pearson correlation coefficient of two expressions.**COUNT()**– Returns the number of items in a group. Null values are not counted.– Returns the number of distinct items in a group. Null values are not counted.COUNTD ()– Returns theCOVAR ()*sample covariance*of two expressions.– Returns theCOVARP ()*population covariance*of two expressions.**MAX()**– Returns the maximum ofan expression across all records. If the expression is a string value, this function returns the last value where last is defined by alphabetical order.**MEDIAN()**– Returns the median ofan expression across all records. Median can only be used with numeric fields. Null values are ignored.**MIN()**– Returns the minimum ofan expression across all records. If the expression is a string value, this function returns the first value where first is defined by alphabetical order.**PERCENTILE()**– Returns the percentile value from the given expression corresponding to the specified number.**STDEV()**– Returns the statistical standard deviation of all values in the given expression based on a sample of the population.**STDEVP()**– Returns the statistical standard deviation of all values in the given expression based on a biased population.**SUM()**– Returns the sum of all values in the expression. SUM can be used with numeric fields only. Null values are ignored.**VAR()**– Returns the statistical variance of all values in the given expression based on a sample of the population.**VARP()**– Returns the statistical variance of all values in the given expression on the entire population.

Aggregating data is key to generating concise and performant dashboards, so

**USER FUNCTIONS**

**FULLNAME()**– Returns the full name for the current user. This is the Tableau Server or Tableau Online full name when the user is signed in; otherwise the local or network full name for the Tableau Desktop user.**ISFULLNAME()**– Returns true if the current user’s full name matches the specified full name, or false if it does not match. This function uses the Tableau Server or Online full name when the user is signed in; otherwise it uses the local or network full name for the Tableau Desktop user.**ISMEMBEROF()**– Returns true if the person currently using Tableau is a member of a group that matches the given string. If the person currently using Tableau is signed in, the group membership is determined by groups on Tableau Server or Tableau Online. If the person is not signed in, this function returns false.**ISUSERNAME()**– Returns true if the current user’s username matches the specified username, or false if it does not match. This function uses the Tableau Server or Online username when the user is signed in; otherwise it uses the local or network username for the Tableau Desktop user.**USERDOMAIN()**– Returns the domain for the current user when the user is signed on to Tableau Server. Returns the Windows domain if the Tableau Desktop user is on a domain. Otherwise this function returns a null string.**USERNAME()**– Returns the username for the current user. This is the Tableau Server or Tableau Online username when the user is signed in; otherwise it is the local or network username for the Tableau Desktop user.

These are really useful and I use them often to provide additional auditing information in a dashboard, or for row level security or use based filtering.

This is not a full list but hopefully will highlight the vast majority of basic functions that are available to you. For a full, comprehensive and managed list, please visit the Tableau Official Documentation at https://onlinehelp.tableau.com/current/pro/desktop/en-us/functions.htm

## Summary

In this part of our introductory series, we explored the Basic Calculations and the various available options. I would suggest connecting to your data source and having a play around with the different formulas to get used to the syntax as well as what is available.

I hope you enjoyed this tutorial, and look forward to the next one. I am always looking for ways to help share knowledge around the community, so if you want to know more about something, and wish there was a tutorial out there, do get in touch on Twitter at @Tableau_Magic.

- Introduction to Tableau (
**TAB101**). - Installation of Tableau Desktop (
**TAB102**). - Data Sources and Connectivity (
**TAB103**). - Building Worksheets (
**TAB104**).- Exploring Marks (
**TAB105**). - Exploring Calculated Fields (
**TAB106**). - Exploring Filters (
**TAB107**) - Exploring Parameters (
**TAB108**). - Exploring Sets (
**TAB109**).

- Exploring Marks (
- Building Dashboards (
**TAB110**).- Exploring Layouts (
**TAB111**). - Exploring Actions (
**TAB112**).

- Exploring Layouts (
- Advanced Calculations:
- Table Calculations (
**TAB113**) - Level of Details (LOD) (
**TAB114**)

- Table Calculations (

Hey there,

I have two fields one for ambition and then another field for alternate ambition. I want to write an if statement that says if alternate ambition is greater then 1, replace the value of the ambition field with the value that is present in the alternate ambition. I would really appreciate some help with this.

Hi Shelly, can you please send me an email to admin@tableaumagic.com, and I will try to have a look at it, conversely, if you post a message to our Facebook Group Tableau Magicians, there are a lot of people there to help you out.