How to Use Window Functions in Bold BI

Published:

Business Intelligence (BI) dashboards are essential tools for analyzing data and gaining insights that can help organizations make informed decisions. One powerful feature of SQL that can be used within BI dashboards is window functions. These functions allow you to perform calculations on a set of rows that are related to the current row, without collapsing them into a single output row.

Window functions can be used in Business Intelligence (BI) dashboards to create complex aggregations, compute running totals, moving averages, and more, which provide valuable insights into data.

This article provides an overview of window functions and demonstrates how they can be implemented in Bold BI dashboards using the expression designer and code view mode.

Introduction to SQL Window Functions

SQL window functions are a powerful feature that allows you to perform calculations across a set of rows that are related to the current row. Unlike regular aggregate functions, window functions do not collapse the rows into a single output row; they maintain the individual rows while still allowing calculations across them.

What is a Window Function in SQL?

A window in SQL refers to a set of rows in a result set that a window function operates on. This set can be the entire result set, a partition of the result set, or a frame within the partition.

A function in SQL is a pre-defined operation that performs a specific task and returns a value. Functions can be used to manipulate data items, perform calculations on data, format dates, and numbers, or transform strings.

A window function in SQL is a type of function that performs a calculation across a set of rows that are somehow related to the current row. This is similar to an aggregate function, but while an aggregate function returns a single value for a group of rows, a window function returns multiple rows with each row containing a calculated value.

Types of Window Functions in SQL

There are several types of window functions in SQL:

Aggregate Window Functions: Perform calculations across a set of rows and return a single aggregated value per row. Examples include SUM(), AVG(), MIN(), MAX(), etc.
Value Window Functions: Return information about the value of the current row compared to other rows in the window. Examples include LEAD(), LAG(), etc.
Ranking Window Functions: Assign a rank to each row within a partition of a result set. Examples include RANK(), DENSE_RANK(), ROW_NUMBER(), etc.

Syntax for Window Function

The basic syntax for a window function is as follows:

<window_function> OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC]]
    [frame_clause]
)

PARTITION BY divides the result set into partitions to which the window function is applied.
ORDER BY specifies the logical order within each partition.
frame_clause defines the subset of rows within the partition to be used for each calculation.

Example of a Window Function in SQL

Let’s consider a scenario where we want to analyze the sales data of a company and compare the performance of each salesperson against the average sales of their respective regions.

We can use the AVG() window function to calculate the average sales while keeping the individual sales records intact.

Here is a simple SQL query that demonstrates the use of a window function:

SELECT
    SalespersonID,
    Region,
    SalesAmount,
    AVG(SalesAmount) OVER (PARTITION BY Region) AS RegionAvgSales
FROM
    SalesRecords;

In this query:

SalespersonID is the identifier for each salesperson.
Region is the geographical area where the salesperson operates.
SalesAmount is the amount of sales made by the salesperson.
AVG(SalesAmount) OVER (PARTITION BY Region) is the window function that calculates the average sales amount for each region. The PARTITION BY clause groups the rows by region, and the AVG() function is applied to each group.

The result of this query will be a list of all sales records, with an additional column showing the average sales for the region of each salesperson.

This allows for easy comparison between individual performance and regional averages directly within a dashboard.

Utilizing Window Functions in Bold BI

Bold BI does not have direct inbuilt support for window functions through Expression functions, it is possible to achieve similar results by using custom functions within the Bold BI expression editor. Additionally, the code view mode can be leveraged for more complex scenarios.

Steps to Use Custom Functions in Bold BI Expression Designer

Open the Expression Designer: Navigate to the data source designer page of your dashboard in Bold BI and open the Expression Designer dialog.
Create a Custom Function: In the expression designer, you can define your custom function that mimics the behavior of the window function you need.

Now, configure the required fields in the widget.

Leveraging Code View Mode

For more advanced users, the code view mode in Bold BI offers a way to write more complex expressions and functions that can simulate the behavior of window functions. This mode provides a more code-centric interface for defining and applying functions to your data.

Conclusion

Window functions are a powerful tool in SQL for performing advanced data analysis. When integrated into BI dashboards, they can provide valuable insights into business performance, trends, and comparisons. By mastering window functions, organizations can take full advantage of their data to drive strategic decision-making.

Additional References

Was this article useful?

Dislike

Written by Venkataprasad Subramani

Updated

Comments (0)