If you’re like me and didn’t have any experience writing calculations before joining The Data School, I’m sure you’re used to seeing errors and red squiggly lines. This is a beginner's guide to writing calculations, whether it's creating calculated fields in Tableau or Filters in Alteryx, this guide gives some general guidelines to cover it all.
There are four basic components to calculations, although not all calculations need to include all four:
Fields – Columns from your data source denoted inside square brackets []
Literal expressions – Constant values that are shown as types denoted inside quotation marks “”
Operators – Symbols that denote an operation
Functions – Statements used to transform the values or members in a field
Consider the following calculation:
The components of this calculation can be broken down as follows:
Fields: [Sales]
Literal expressions: “Profitable”, “Even” and “Loss”
Operators: > and >
Functions: IF, THEN, ELSEIF and END.
Operators
Many basic operators are available when writing calculations in both Tableau and Alteryx, most of which are fairly obvious. These include + (addition), - (subtraction), * (multiplication) and / (division) as well as =, <, >, <=, >= (comparison) operators. One useful and less obvious operator is the !, used to represent not within an operation i.e. != would mean not equal to.
Functions
Both Tableau and Alteryx contain a library of functions and fields that can be selected and automatically placed within your expression. This helps if you’re unsure of the correct syntax. Here’s a list of some basic functions to help you get started:
|
Functions |
Syntax |
How it works |
|
IF, THEN,
ELSE, ELSEIF |
IF
<expr> THEN <then> ELSEIF <expr2> THEN <then2> ELSE
<else> END |
Tests a
series of expressions returning the <then> value for the first true
<expr> |
|
END |
IF
<expr> THEN <then> ELSEIF <expr2> THEN <then2> ELSE
<else> END |
Must be
placed at the end of an IF expression |
|
AND |
IF
<expr1> AND <expr2> THEN <then> END |
Connects the
expressions so both must be true to give <then> |
|
OR |
IF
<expr1> OR <expr2> THEN <then> END |
Disconnects
both expressions so either or both can be true to give <then> |
|
IIF |
IIF(test,
then, else) |
Tests if a
Boolean function is met and returns then if true and else if false. |
|
\\ (Comments) |
\\
calculation incomplete |
Using \\
allows you to write a comment which will not affect the calculation |