Tableau SQL Common Scenarios

YTD & MTD Totals, Conditional Calculations, Rolling Averages, Hierarchical Relationships within Tableau.

A. Calculating year-to-date (YTD) and month-to-date (MTD) totals:

YTD Sales: WINDOW_SUM(SUM([Amount])) OVER (PARTITION BY [Year] ORDER BY [Date] ROWS UNBOUNDED PRECEDING)
 

Calculates the sum of the "Amount" column in the "Sales" table for the year-to-date period based on the "Date" column in the "Calendar" table.

MTD Revenue: WINDOW_SUM(SUM([Amount])) OVER (PARTITION BY [Year], [Month] ORDER BY [Date] ROWS UNBOUNDED PRECEDING)
 

Calculates the sum of the "Amount" column in the "Revenue" table for the month-to-date period based on the "Date" column in the "Calendar" table.

B. Applying conditional calculations using CASE statement:

Conditional Sales: IF [Amount] > 1000 THEN "High" ELSE "Low" END
 

Checks if the value in the "Amount" column of the "Sales" table is greater than 1000. If true, it returns "High"; otherwise, it returns "Low".

Category Group: CASE [Category] WHEN 'Electronics' THEN 'Technology' WHEN 'Clothing' THEN 'Apparel' ELSE 'Other' END
 

Evaluates the value in the "Category" column of the "Sales" table. If the value matches "Electronics," it returns "Technology." If it matches "Clothing," it returns "Apparel." Otherwise, it returns "Other."

C. Implementing rolling averages and moving totals:

3-Month Rolling Average: WINDOW_AVG(SUM([Amount])) OVER (PARTITION BY [Year], [Month] ORDER BY [Date] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
 

Calculates the average of the "Amount" column in the "Sales" table over a rolling period of 3 months, based on the "Date" column in the "Calendar" table.

7-Day Moving Total: WINDOW_SUM(SUM([Amount])) OVER (ORDER BY [Date] ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
 

Calculates the sum of the "Amount" column in the "Sales" table over a moving period of 7 days, based on the "Date" column in the "Calendar" table.

D. Handling hierarchical and parent-child relationships:

Parent Total: WINDOW_SUM(SUM([Sales])) OVER (ALL([Department]))
 

Sums up the "Sales" column in the "Departments" table for all related rows.

Descendants Count: WINDOW_COUNT(ATTR([Product])) OVER (ALL([Category]))
 

Counts the number of rows in the "Products" table that are descendants of a specific category in the "Category" column, including the category itself.

E. Creating advanced calculated tables and columns:

Calculated Table: { FIXED [Category] : SUM([Amount]) }
 

Creates a new table that summarizes the "Sales" table by the "Category" column and calculates the total sum of the "Amount" column for each category.

Calculated Column: [Discounted Price] = [Price] * (1 - [Discount])
 

Calculates the "Discounted Price" by multiplying the values in the "Price" column and (1 - Discount) in the "Sales" table. The result is stored in a new column called "Discounted Price."