Tableau

Posted : admin | On 17-09-2021

This article introduces logical functions and their uses in Tableau. It also demonstrates how to create a logical calculation using an example.

Tableau Public is free software that can allow anyone to connect to a spreadsheet or file and create interactive data visualizations for the web. In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau. Navigate to a worksheet. From the Data pane, drag State to the Rows shelf. From the Data pane, drag Category to the Rows shelf and place it to the right of State. From the Data pane, drag Sales to the Columns shelf. Select Analysis Create.

Tableau

Why use logical calculations

Logical calculations allow you to determine if a certain condition is true or false (boolean logic). For example, you might want to quickly see if sales for each country you distribute your merchandise to were above or below a certain threshold.

The logical calculation might look something like this:

SUM(Sales) > 1,000,000

Logical functions available in Tableau:

Function

Syntax

Description

IN<expr1> IN <expr2>

Returns TRUE if any value in <expr1> matches any value in <expr2>.

The values in <expr1> can be a Set, list of literal values, or a combined field.

Examples:

SUM([Cost]) IN (1000, 15, 200)

[SET] IN [COMBINED FIELD]

AND

IF <expr1> AND <expr2> THEN <then> END

Performs a logical conjunction on two expressions.

Example:

IF (ATTR([Market]) = 'Africa' AND SUM([Sales]) > [Emerging Threshold] )THEN 'Well Performing'

CASECASE <expression> WHEN <value1> THEN <return1> WHEN <value2> THEN <return2> ... ELSE <default return> END

Performs logical tests and returns appropriate values. The CASE function evaluates expression, compares it to a sequence of values, value1, value2, etc., and returns a result. When a value that matches 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.

CASE is often easier to use than IIF or IF THEN ELSE.

Typically, you use an IF function to perform a sequence of arbitrary tests, and you use a CASE function to search for a match to an expression. But a CASE function can always be rewritten as an IF function , although the CASE function will generally be more concise.

Many times you can use a group to get the same results as a complicated case function.

Examples:

CASE [Region] WHEN 'West' THEN 1 WHEN 'East' THEN 2 ELSE 3 END

CASE LEFT(DATENAME('weekday',[Order Date]),3) WHEN 'Sun' THEN 0 WHEN 'Mon' THEN 1 WHEN 'Tue' THEN 2 WHEN 'Wed' THEN 3 WHEN 'Thu' THEN 4 WHEN 'Fri' THEN 5 WHEN 'Sat' THEN 6 END

ELSEIF <expr> THEN <then> ELSE <else> END

Tests a series of expressions returning the <then> value for the first true <expr>.

Example:

If [Profit] > 0 THEN 'Profitable' ELSE 'Loss' END

ELSEIFIF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END

Tests a series of expressions returning the <then> value for the first true <expr>.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END

ENDIF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END

Tests a series of expressions returning the <then> value for the first true <expr>. Must be placed at the end of an expression.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END

IFIF <expr> THEN <then> [ELSEIF <expr2> THEN <then2>...] [ELSE <else>] END

Tests a series of expressions returning the <then> value for the first true <expr>.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Breakeven' ELSE 'Loss' END

IFNULLIFNULL(expr1, expr2)

Returns <expr1> if it is not null, otherwise returns <expr2>.

Example:

IFNULL([Profit], 0)

IIFIIF(test, then, else, [unknown])

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.

Example:

IIF([Profit] > 0, 'Profit', 'Loss')

ISDATEISDATE(string)

Returns true if a given string is a valid date.

Example:

ISDATE('2004-04-15') = True

ISNULLISNULL(expression)

Returns true if the expression is NULL (does not contain valid data).

Example:

ISNULL([Profit])

MAXMAX(expression) or Max(expr1, expr2)

Returns the maximum of a single expression across all records or the maximum of two expressions for each record.

Example:

MAX([Sales])

MINMIN(expression) or MIN(expr1, expr2)

Returns the minimum of an expression across all records or the minimum of two expressions for each record.

Example:

MIN([Profit])

NOTIF NOT <expr> THEN <then> END

Performs logical negation on an expression.

Example:

IF NOT [Profit] > 0 THEN 'Unprofitable' END

ORIF <expr1> OR <expr2> THEN <then> END

Performs a logical disjunction on two expressions.

Example:

IF [Profit] < 0 OR [Profit] = 0 THEN 'Needs Improvement' END

THENIF <expre> THEN <then> [ELSEIF ,expr2> THEN <then2>...] [ELSE <else>] END

Tests a series of expressions returning the <then> value for the first true <expr>.

Example:

IF [Profit] > 0 THEN 'Profitable' ELSEIF [Profit] = 0 THEN 'Break even' ELSE 'unprofitable' END

WHENCASE <expr> WHEN <Value1> THEN <return1> ... [ELSE <else>] END

Finds the first <value> that matches <expr> and returns the corresponding <return>.

Example:

CASE [RomanNumberal] WHEN 'I' THEN 1 WHEN 'II' THEN 2 ELSE 3 END

ZNZN(expression)

Returns <expression> if it is not null, otherwise returns zero.

Example:

ZN([Profit])


Create a logical calculation

Tableau

Template

Follow along with the steps below to learn how to create a logical calculation.

  1. In Tableau Desktop, connect to the Sample - Superstore saved data source, which comes with Tableau.

  2. Navigate to a worksheet.

  3. From the Data pane, drag State to the Rows shelf.

  4. From the Data pane, drag Category to the Rows shelf and place it to the right of State.

  5. From the Data pane, drag Sales to the Columns shelf.

  6. Select Analysis > Create Calculated Field.

  7. In the calculation editor that opens, do the following:

    • Name the calculated field, KPI.

    • Enter the following formula:

      SUM([Profit]) > 0

      This calculation quickly checks if a member is great than zero. If so, it returns true; if not, it returns false.

    • When finished, click OK.

      The new calculated field appears under Measures in the Data pane. Just like your other fields, you can use it in onr or more visualizations.

  8. From the Data pane, drag KPI to Color on the Marks card.

    You can now see which categories are losing money in each state.

See Also

Thanks for your feedback!

Tableau Download

Tableau software is a powerful business intelligence, visualization, and analytics tool. Tableau has been one of the Gartner's 'Leaders' in the business intelligence software field. There are two components:

  • Tableau Desktop helps users create and publish dashboards and data visualizations and perform data analysis. Tableau Desktop is available for a free trial from Tableau. Licensing for the software is maintained by ITS. To purchase a license, please visit the Tableau Software page for more information. Purchasing Tableau Desktop requires a one-time fee plus an annual maintenance fee (which gives you access to tech support and all free upgrades).
  • Tableau Server lets desktop license holder share data visualizations that can be viewed and, in some cases, manipulated by authorized users in the university community. Tableau Server is the best way to publish and share reports with other university users.

In addition to an interface that is easy to use for both those who publish visualizations and those who use them, Tableau enables data from multiple sources to be integrated together into combined reports. These visualizations can show combinations of locally maintained data, enterprise data, and even public data.