Articles in this section
Category / Section

How to use LAG function in query to create dashboard?

Updated:

Bold BI allows you to create the calculated fields using SQL queries such as Excel calculated fields. Calculated fields also known as Expression will also be used to create your dashboard in Bold BI.

Calculating percentage with the previous field

In Excel

To calculate percentage value by comparing the current month value with previous month value, you need to use the following formula in Excel sheet.

Sample Excel

You can calculate the same percentage value in Bold BI using the Custom query.

How to calculate LAG Function in Bold BI

Bold BI application uses an intermediate database to store your Excel data in the backend. So, it is possible for you to create a custom query for your dashboard which uses the Excel connection. Follow the below steps to recreate the pre-calculated KPIs of Excel in the Bold BI application.

NOTE: We have supported intermediate databases such as SQL/Azure SQL, MySQL, and PostgreSQL.

  1. Create an Excel data source. While connecting the data source, your Excel data will be extracted and created as a table in the Intermediate database which you have used in the Data Store Settings.

    Create an Excel data source

  2. Drag and drop your table in Design canvas and switch to Code View.

    Code view

  3. Use LAG analytic function to access the data from the previous row and add it as a virtual column using the queries as follows:
    1. SQL:

      SQL

    2. MySQL:

      MySQL

    3. PostgreSQL:

      PostgreSQL

    4. Configure the added virtual column in widgets as follows.

      Widget Configuration

    5. Go to the Properties tab of the Grid widget and added KPI columns to show the computed values. Click Add button under Columns Settings to add the KPI columns to your widgets.

      Add KPI button

    6. Add the KPI Expression as follows.

      KPI Expression

    7. You can add N number of KPI Expression column by clicking Add button.

      KPI Columns

    8. The following grid widget is the sample widget which shows the comparison of the current month and previous month values in percentage, and they are equivalent to the Excel data.

      Sample widget

    9. You can also configure more widgets to show overall percentage of a column, the number of new visits count, page visits, new users count, new comments count, and more to create your dashboard.

      Sample Dashboard

Related links

Was this article useful?
Like
Dislike
Help us to improve this page
Please give feedback or comments
RN
Written by Renuka N
Updated
Comments (0)
Please sign in to leave a comment