Calculations in Tableau

Tableau provides the different types of calculation options, operators, and functions, using these options we can perform different types of operations.

Following are the types of Tableau Calculations :

  • Tableau Operators
  • Tableau Functions
  • Table Calculations
  • Numeric Calculations
  • String Calculations
  • Date Calculations
  • LOD Expressions

Introduction to Tableau

Tableau Operators

Tableau operators are symbols that tell the compiler to perform different types of mathematical and logical operations.

Tableau provides a number of operators that are used to create calculated fields and formulas.

General Operators helps to perform Mathematical, Logical, and Relational operations.

Tableau provides the different types of general operators, these operators can be used to perform on different kinds of data types, such as Numerics, Characters, and Date.

The following table shows the general operators with description:

Operator Description
"+"(Plus)
  • The + operator is used to add two numbers.
    Example: 5+6=11
  • Used to Concatenate strings.
    Example: "Apple"+"Orange"
  • Used to add Days to the Date data type.
    Example: #Jan 15, 2020#+16=#Jan31.
"-"(Subtraction)
  • The "-" is used to subtract two numbers.
    Example: 10-7=3.
  • Used to subtract days from the date.
    Example: #jan31, 2020#-15 = #Jan 16

Arithmetic Operators:

  • Arithmetic operators are those which are used to perform the operation only on numerical values.
  • The following table describes the different types of Arithmetic operators and description.
    Operator Description
    *(Multiplication)
    • This operator is used to perform multiplication operations on numeric values.
      Ex: 2*3= 6
    /(Division)
    • This operator is used to perform Division operations on numeric values.
      Ex: 25/5=5
    ^Power
    • This operator is used to find the power of the values.
      Ex: 2^3=8
    %Madulo
    • This operator is used to find the reminder of numeric division.
      Ex: 15/2=1

Logical Operators:

The logical operators are used to perform operations on expressions, which results in boolean value as True or False.

The following table describes the different types of Logical operators and description.

Operators Description
And(&)
  • If the expressions or Boolean values present on both sides of the AND operator are evaluated to be TRUE, then the result is TRUE. Else the result is FALSE.
    Ex: If a = 5 and b = 2 then, expression
    ((c==5) && (d>5)) equals to 0.
OR(||)
  • Logical OR. True only if either one operand is true.
    Ex: If c = 5 and d = 2 then, expression ((c==5) || (d>5)) equals to 1.
Not
  • Logical NOT. True only if the operand is 0.
    Ex: If c = 5 then, expression !(c==5) equals to 0.

Relational Operators:

The relational operators are used in expressions. Each operator compares two numbers, dates, or strings and returns a Boolean value (TRUE or FALSE). Booleans themselves, however, cannot be compared using these operators.

The following table describes the different types of relational operators and description.

Operator Description
==(equal to)
  • Compares two numbers or two strings or two dates to be equal. Returns the Boolean value TRUE if they are, else returns false.
    Ex: "Hello" = "Hello" 5 = 15/ 3
!=(Not equal to)
  • Compares two numbers or two strings or two dates to be unequal. Returns the Boolean value TRUE if they are, else returns false.
    Ex: "Good" <> "Bad" 18 != 37 / 2
>( Greater than)
  • Compares two numbers or two strings or two dates where the first argument is greater than the second. Returns the boolean value TRUE if it is the case, else returns false.
    Ex: [Profit] > 20000 [Category] > ‘Q’ [Ship date] > #April 1, 2004#
<(Less than)
  • Compares two numbers or two strings or two dates where the first argument is smaller than the second. Returns the boolean value TRUE if it is the case, else returns false.
    Ex: [Profit] < 20000 [Category] < ‘Q’ [Ship date] < #April 1, 2004#

Installation of Tableau

Tableau Functions

Tableau has a number of inbuilt functions that help in creating expressions for complex calculations.

Number Functions:

The number functions are used to perform only numeric calculations. We can use only numbers as inputs to perform this operation.

The following table describes the important number of functions :

Functions Description
CEILING (number)
  • This function is used to round a number to the nearest integer of equal or greater value.
    Ex: CEILING(4.897)=5
POWER (number, power)
  • This function is used to Raise the number to the specified power.
    Ex: POWER(3,3) = 37
ROUND (number, [decimals])
  • This function is used to round the decimal numbers to a specified number of digits.
    Ex: Round(8.134567,67)=8.13

String Functions:

The string Functions are used for string manipulation. The following table gives some important string functions with descriptions.

String Functions Description
LEN(String)
  • This function is used to return the length of the string.
    Ex: LEN("CherCherTech")=12
LTRIM(String)
  • This function returns the string with any leading spaces removed.
    Ex: LTRIM(" Chercher") = "Chercher"
RTRIM(String)
  • This function returns the string with any Trailing spaces removed.
    Ex:RTRIM("CherCher ")="CherCher"
TRIM(String)
  • This function returns the string with both leading and trailing spaces removed.
    Ex:TRIM(" CherCher ")="CerCher"
REPLACEMENT(String, Substring, replacement)
  • This function is used to replace the substring with replacement in the given string. If the substring is not found, the string is not changed.
    Ex: REPLACE("AppleBananaGrapes", "Banana", "Beetrrot") = "AppleBetrootGrapes"
MIN(a,b)
  • Returns the minimum of 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. It returns Null if either argument is Null.
    Ex: MIN ("Apple","Banana") = "Apple"
MX(a,b)
  • Returns the maximum of 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. It returns Null if either argument is Null.
    Ex: MAX ("Apple","Banana") = "Banana"
SPACE(Number)
  • Returns a string that is composed of the specified number of repeated spaces.

Ex: SPACE(1)= " "

UPPER(string)
  • This function returns the string, with all characters uppercase.
    Ex: Upper("calculations")="CALCULATIONS"

Date Functions:

Tableau provides a variety of date functions to carry out calculations involving dates. All the date functions use the date_part which is a string indicating the part of the date such as a month, day, or year.

The following table lists some of the important date functions :

Date Functions Description
DATEADD (date_part, increment, date)
  • This function returns an increment added to the date. The type of increment is specified in date_part.
    Ex: DATEADD ('month', 2, #2016-02-29#) = 2016-04-29 12:00:00 AM
DATENAME (date_part, date, [start_of_week])
  • This function returns date_part of date as a string. The start_of_week parameter is optional.
    Ex: DATENAME('month', #2004-04-15#) = "April" DATENAME('year', #2004-04-15#)="2004" DATENAME('Day',#2004-04-25#)="25"
DAY (date)
  • This function returns the day of the given date as an integer.
    Ex: DAY(#2017-03-09#) = 09
NOW( )
  • This function returns the current date and time.
    Ex: NOW()= 2017-03-09 1:08:21 PM

Logical Functions:

The Tableau provides some logical functions, that are used to evaluate some single value or the result of an expression and produce a boolean output.

Logical Functions Description
IFNULL (expression1, expression2)
  • The IFNULL function returns the first expression if the result is not null, and returns the second expression if it is null.
    Ex: IFNULL([Chercher],0)=[Chercher]
ISDATE (string)
  • The ISDATE function returns TRUE if the string argument can be converted to a date, and FALSE if it cannot.
    Ex: ISDATE("20/01/92")=True,
    ISDATE("12/05/98") = FALSE
MIN(expression)
  • The MIN function returns the minimum of an expression across all records or the minimum of two expressions for each record.

Aggregate Functions:

Aggregate Functions are a type of function where values of multiple rows are grouped together as the input to form a single value of more significant meaning, such as a set or list.

The following table gives you some of the aggregate functions and description :

Aggregate Functions Description
AVG(expression)
  • This function returns the average of all the values in the expression. AVG can be used with numeric values only. Null values are ignored.
COUNT (expression)
  • This function returns the number of items in the group except for null values.
MEDIAN (expression)
  • This function returns the median of an expression across all records. The Median can only be used with numeric values, except the null values.
STDEV (expression)
  • This function returns the statistical standard deviation of all the values in a given expression based on a sample of the population.

Tableau Workspace and Navigation

Table Calculations in Tableau

Table calculation is the one that can be applied to the entire table.

For example, for calculating a running total or running average, we need to apply a single method of calculation to an entire column. Such calculations cannot be performed on some selected rows.

The table has a feature called Quick Table Calculation, which is used to create this kind of calculation.

The steps to perform a Quick Table calculation:
  1. Select the measure variable on which the table calculation has to be applied and drag it to the column shelf.
  2. Right-click the measure variable and choose the option Quick Table Calculation.
  3. Choose one of the following options to be applied to the measure.
    • Running Total
    • Difference
    • Percent Difference
    • Percent of Total
    • Rank
    • Percentile
    • moving average
    • Year to Date (YTD) Total
    • Compound Growth Rate
    • Year over Year Growth
    • Year to Date (YTD) Growth
Creating a quick table for profit earned for the data source:
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigated to the downloaded files, select Sample Superstore.xls excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls excel file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • The Sample Superstore excel file contains the below details.
    sample-superstore-excel-file-tableau
  • Drag the profit variable to the column shelf from the measure.
    drag-profit-measure-to-column-shelf-tableau
  • Right-click on the measure value in the column shelf and select Quick Table Calculation.
  • And, select the Running Total option.
    select-running-total-under-quick-table-tableau
  • Now you will find the below graph which shows the running sum of profit for all the given values.
    graph-with-running-sum-of-profit-tableau

Tableau Data Relationship

Numeric Calculations in Tableau

Tableau provides a wide range of inbuilt functions to perform numeric calculations. These inbuilt functions are available in the formula editor.

We have to create a calculation field to perform numeric calculation, and the calculation is as simple as subtracting the two values or applying an aggregate function to a single field.

Steps to create a calculation field and add numeric functions :
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigated to the downloaded files, select Sample Superstore.xls excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a sample text file and excel file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • The Sample Superstore excel file contains the below details.
    sample-superstore-excel-file-tableau
  • Once you connected, drag the Orders sheet to the Tableau workspace.
    connect-datasource-and-drag-orders-tableau
  • Next, click on Sheet1, it will navigate you to the Sheet1 page.
    clicki-on-sheet1-navigates-to-worksheet-tableau
  • Click Analysis and select Create Calculation Field.
    select-create-calculation-fielf-under-analysis-tableau
  • The above step opens the Calculation window.
    calculation-window-tableau
  • Click on the small triangle symbol inside the calculation window, You will navigate to the Calculation editor window as below.
    calculation-editor-window-tableau
  • To study the difference between profit and discount for different shipping mode of the products, create a formula for subtracting the discount from the profit as shown below.
  • Also, name this field as profit_n_discount.
    Profit-[Discount]
  • Once you click on Ok, the profit_n_discount field has been created under the measure as shown below.
    profit-and-dicount-field-tableau
  • Now, drag the Ship-Mode to the column shelf and profit_n_discount field to the rows shelf, The Tableau will produce the bar chart as shown below.
  • Which shows the difference between the profit and discount for different shipping mode.
    barchart-with-different-shipmode-tableau

Data Sorting in Tableau

String Calculations in Tableau

Tableau provides many inbuilt string functions that are used for string manipulation such as Cancatenation, Comparision, and replacing the substring.

Creating a calculation field and using string function:
  • Follow the first 5 steps from the numeric calculation part.
  • Once the calculation editor page will open, which lists all the functions available in Tableau.
  • You can change the dropdown value to CONTAINS and see only the functions related to strings.
    function-contains-only-string-function-tableau
  • Consider you want to find out the sales in the cities, which contain the letter "O".
  • Create the formula for the same as shown below, and enter the below formula in the calculation field and click on OK.
    CONTAINS([city],"o")​

    formula-to-find-sales-in-city-tableau

  • The new calculation field has been created under the Dimension. You can drag the City variable to the row shelf and drag the Sales to the column shelf.
    drag-sales-and-city-to-column-and-rows-tableau
  • Drag and drop the city_o field to filter shelf, a Filter[city_o] window will open, select True in that and click on OK.
    drag-city-o-to-filter-shelf-tableau
  • The below graph will appear.
    bar-graph-with-sales-in-each-city-tableau

Tableau Worksheets

Date Calculation in Tableau

Tableau provides a large number of inbuilt date functions that are used to manipulate dates such as adding or subtracting days from a date. You can also create complex expressions involving dates.

Date Calculation function:
  • Follow the first 5 steps from the numeric function.
  • Once the calculation editor field has been opened, you can change the dropdown value to Date and can see the functions related to Date.
    date-function-tableau
  • Here, we have to find out the sales volume along with the difference in the date of sales in months from 21st March 2009. For this, create the formula as shown below.
    DATEDIFF('month',#2009-03-21#,[Ship Date])​
  • Enter the above formula inside the calculation table and then click OK.
    formula-inside-date-diff-calculation-table
  • Now, the new field date_diff has been created under the Measure.
    new-date-diff-field-created-under-measure-tableau
  • Right-click on date_diff filed and select convert.
    select-convert-to-dimenstion-tableau
  • Drag Ship_Date with Month and date_diff to row shelf and Sale to the column shelf respectively, you will find the below graph which shows, sales volume along with the difference in the date of sales in months from 21st March 2009.
    date-function-with-sales-value-tableau
0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions