KNOWLEDGE BASE

Using Raw SQL Functions


Product(s): Tableau Desktop
Version(s): All
Last Modified Date: 16 Aug 2016

You can use raw SQL (pass-through) functions to find 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 the 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 on 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, 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 only those within the filter. In this situation, 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.

 

Alternate Search Terms:Calculations Filters
Did this article resolve the issue?