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.