Search

Search Scripts

Scripting Reference Guides

Rick W
/ Categories: Tableau, Advanced Functions

Tableau SQL Advanced Functions

Advanced Functions within Tableau.


A. WINDOW_SUM:
TotalSales = WINDOW_SUM(SUM([Amount]), FIRST()==0 AND LAST()==0 AND ATTR([Year]) = 2022)

This example calculates the total sales amount from the Sales table for the year 2022. The WINDOW_SUM function modifies the window context by applying conditions to the Year column.

B. WINDOW_MAX:
HighValueProducts = WINDOW_MAX(IIF([Price] > 1000, [ProductID], NULL))

This example filters the Products table to retrieve only the products with a price greater than 1000. The WINDOW_MAX function selects the maximum value for the specified condition.

C. WINDOW_SUM:
TotalSalesAllYears = WINDOW_SUM(SUM([Amount]), FIRST()==0 AND LAST()==0)

This example calculates the total sales amount from the Sales table, removing any filters applied to the Year column. The WINDOW_SUM function modifies the window context to consider all years for the calculation.

D. ATTR:
ProductCategory = ATTR(IF [Year] = 2022 THEN [CategoryName] END)

This example retrieves the CategoryName value from the Categories table that is related to the current row in the data model. The ATTR function fetches values from a related table based on the defined relationships.

E. Advanced Table Functions:

1. SUMMARIZE:
SUMMARIZE([Table1], [Column1], [Table2].[Column2], "Total", SUM([Table1].[Amount]))

Creates a summary table with aggregated values. It groups the data by values in "Column1" from "Table1" and "Column2" from "Table2". It also includes a calculated column called "Total" that sums the "Amount" column from "Table1".

2. CROSS JOIN:
CROSS JOIN([Table1], [Table2])

Combines "Table1" and "Table2" to generate all possible combinations of rows between the two tables.

3. VALUES:
VALUES([Table1].[Column1])

Retrieves distinct values from the "Column1" column in "Table1" within the current filter context.

F. Using Advanced Aggregation Functions:

1. WINDOW_RANK:
WINDOW_RANK([Table1].[Column1])

Ranks values in "Column1" of "Table1" based on the values in the same column.

2. WINDOW_PERCENTILE:
WINDOW_PERCENTILE([Table1].[Column1], 0.75)

Calculates the value at the 75th percentile within "Column1" of "Table1".

G. Implementing Time Intelligence Calculations with SQL:

1. RUNNING_SUM:
RUNNING_SUM(SUM([Amount])) OVER (PARTITION BY [Year] ORDER BY [Date])

Calculates the total year-to-date value by summing the "Amount" column from the "Sales" table based on the "Date" column in the "Calendar" table.

2. LAG:
LAG(SUM([Amount])) OVER (PARTITION BY [Year] ORDER BY [Date])

Retrieves values from the same period in the previous year based on the "Date" column in the "Calendar" table.

3. DATE_RANGE:
SUM([Amount]) FILTER ([Date] >= '2022-01-01' AND [Date] <= '2022-12-31')

Filters data by the date range between January 1, 2022, and December 31, 2022, based on the "Date" column in the "Calendar" table.

Previous Article Tableau SQL Information Functions
Next Article Tableau SQL Common Scenarios
Print
227