How to Display First and Last Values in Bold BI widgets.

Overview

If your requirement is to display the first or last value in a Number Card, Text Widget or KPI Card based on a specific measure or dimension in Bold BI, you can achieve this by using a custom SQL query in the data source.

This approach creates additional columns that return the first and last values based on a specified sorting field, which can then be used directly within a widget.

Sample Scenario

Consider the following customer_orders table:

• The first amount based on the earliest order_date
• The last amount based on the latest order_date

Steps to Retrieve First and Last Values

1. Connect Your Data Source

Create or edit your data source in Bold BI and switch to Code view

2. Execute the Following Query

MYSQL:

SELECT *,
      (
          SELECT amount
          FROM customer_orders
          ORDER BY order_date ASC
          LIMIT 1
      ) AS first_amount,

      (
          SELECT amount
          FROM customer_orders
          ORDER BY order_date DESC
          LIMIT 1
      ) AS last_amount
FROM customer_orders; 

MSSQL:

SELECT
   [customer_orders].[id] AS [id],
   [customer_orders].[customer_id] AS [customer_id],
   [customer_orders].[order_date] AS [order_date],
   [customer_orders].[amount] AS [amount],
   [customer_orders].[status] AS [status],

   (
       SELECT TOP 1 [amount]
       FROM [dbo].[customer_orders]
       ORDER BY [order_date] ASC
   ) AS [first_amount],

   (
       SELECT TOP 1 [amount]
       FROM [dbo].[customer_orders]
       ORDER BY [order_date] DESC
   ) AS [last_amount]

FROM [dbo].[customer_orders] AS [customer_orders] 

PostgreSQL:

SELECT
   customer_orders.id,
   customer_orders.customer_id,
   customer_orders.order_date,
   customer_orders.amount,
   customer_orders.status,

   (
       SELECT amount
       FROM customer_orders
       ORDER BY order_date ASC
       LIMIT 1
   ) AS first_amount,

   (
       SELECT amount
       FROM customer_orders
       ORDER BY order_date DESC
       LIMIT 1
   ) AS last_amount

FROM customer_orders; 

The query creates two calculated fields:

• first_amount
• last_amount

These fields contain the first and last values from the dataset based on the order_date.

3. To display these values in Bold BI:

• Drag and drop a Number Card, Text Widget or KPI Card
• Drag either first_amount or last_amount into the widget’s Value section.
• Since these values are repeated for every record returned by the query, configure the field aggregation as Min or Max.
  
  
• The widget will then display a single value representing the first or last record from the dataset.

Result:

Conclusion

Additionally, if you want to retrieve the first and last values based on any other field, replace the column used in the ORDER BY clause with the desired field. For example, use ORDER BY mark, ORDER BY sales_date, or any other dimension or measure that determines the order of the records.