KNOWLEDGE BASE

Using Raw SQL Functions


Published: 27 Mar 2017
Last Modified Date: 24 Jul 2017

Question

How to use raw SQL (pass-through) functions to identify aggregations for an entire data set, regardless of the filters applied to the view.

When you apply a filter to a view, the field aggregations apply only to values that are within the bounds of that filter.

For example, suppose you have three Sales receipts ($10, $100, $150) and you filter your view to show only receipts over $20. The $10 value is not included in an aggregation of the Sales field of this view. SUM([Sales]) computes to $250, and MIN([Sales]) to $100.

You might want to filter the values that appear in the view, but include the outlying values in calculations. In the example above, you might want to know the percentage of total sales for each transaction, with total sales including all sales in your original data set, not just limited to those within the filter.

Environment

  • Tableau Server
  • Raw SQL Functions

Answer

In this type of situations, you can create two calculated fields:
  • A calculated field that contains a raw SQL function to determine the total sales number.
  • A calculated field to determine each transaction's percentage of the total sales number.

The following procedure uses the Sample - Superstore data source to show how to create these calculated fields.

Step 1 

  1. Select Analysis > Create Calculated Field.

    In the calculation editor, create this formula:

    RAWSQLAGG_REAL("SELECT SUM([Sales]) FROM [Orders$]")

  2. Name the field Total Sales.

    Note: This SELECT statement is formatted for Microsoft JET (Excel and Access). Different database engines have different SQL syntax.

Step 2 

Now create a second calculated field to determine the percentage of total sales for each transaction.

  1. In the calculation editor, create this formula:

    SUM([Sales]) / [Total Sales]

  2. Name the field Percent of Total Sales.

Did this article resolve the issue?