How to use LAG function in query to create dashboard?
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 ExcelTo calculate percentage value by comparing the current month value with previous month value, you need to use the following formula in Excel sheet.
You can calculate the same percentage value in Bold BI using the Custom query.
How to calculate LAG Function in Bold BIBold 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.
- 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.
- Drag and drop your table in Design canvas and switch to Code View.
- Use LAG analytic function to access the data from the previous row and add it as a virtual column using the queries as follows:
- Configure the added virtual column in widgets as follows.
- 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 the KPI Expression as follows.
- You can add N number of KPI Expression column by clicking Add button.
- 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.
- 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.