Window functions are among the most useful inbuilt functions in Tableau. These functions can be used while carrying out aggregation over a certain set of rows of our interest. These set of rows can be called windows. This puts the term ‘Window’ in window functions. They can also be used for carrying out comparative analyses.
The syntax is as follows:
window_function(AGGREGATION(['Measure_Name']),[offset])
If no offset is provided, the window function is applied for the whole table.
In the below example, months with the highest and lowest Profits are highlighted. This gives visual cue to the audience on high and low performing months by setting them apart.
How was this achieved?
This can be achieved by using the window function as follows.
This calculated field checks for the highest and lowest sum of Profit in the provided window. In this case, the entire table is considered.
Upon dragging the calculated field to the ‘Color’ mark, the values are highlighted.
Upon adding the ‘Year’ field, the granularity on the view is changed. However, the highest and lowest Profit values are highlighted considering all months for all 4 years in the view.
The highlighted Profit values can be changed to show highest and lowest values for each of the years in the view. This can be done by Right Clicking on the calculated field,
click on ‘Compute using’ and click on ‘Pane (down)’. Now each of the years in the view is considered as a window. This highlights the highest and lowest Profit values for each year.
So there you have it. A simple way of using window functions for comparative analyses.