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.
228