Using Calculated Fields in Your Dashboard
A calculated field (also known as a calculated column, expression field, or expression column) is a custom column whose value is a computed value of an expression. This field comprises of built-in functions as well as formulas and other columns in the underlying data table. With calculated fields, you can make simple arithmetic operations or complex mathematical and logical operations, type conversions, and more. In Bold BI dashboards, you can create a calculated field with measure or dimension fields from the underlying data source and built-in functions. Then, easily configure that field to certain widgets. Calculated fields play a key role in any business analytics process. This blog post will answer the following questions on calculated fields and visualize them effectively using Bold BI:
- What are the uses of a calculated field?
- When is a calculated field needed?
- How can you use a calculated field?
What are the uses of a calculated field?
- Being an expression-bound column and value computed on the fly, there’s no need to write back to the underlying data. Hence, the data table schema will be maintained.
- An expression can comprise one or more functions combined with data fields in a nested manner. These functions can be categorized as follows:
- Numbers: ABS, ACOS, ASIN, ATAN, COS, DEGREES, EXP, LOG, PI, POWER, RADIANS, SIGN, SIN, SQRT, TAN.
- Aggregation: AVG, COUNT, COUNTD, MAX, MIN, STDEV, SUM, VAR.
- Conditional IF, ISNULL.
- Logical: AND, NOT, OR.
- Date: DATEADD, DATESUB, DAY, DAYDIFF, HOUR, MINUTE, MONTH, NOW, TODAY, YEAR, DATENAME, DATEPART, MAX, MIN.
- String: LEN, CHAR, CONCAT, CONTAINS, ENDSWITH, LEFT, LOWER, LTRIM, MAX, MIN, RIGHT, RTRIM, STARTSWITH, SUBSTR, UPPER.
- A calculated field can be used in widgets like built-in data source fields, either as a measure or as a dimension field based on its computed value.
When is a calculated field needed?
Raw data can either be structured or unstructured. Structured data used to be in normalized form. Visualizing such data through a dashboard for dynamic analysis requires more granularity. So, not all data fields can be utilized as is to represent KPIs in widgets. This is primarily where a calculated field is needed. There are some situations listed below in which the need for a calculated field arises commonly:
- KPI from a combination of multiple columns in a data source, computing formulas, etc.
- Flag to represent dimension values.
- Customized display text.
How can you use a calculated field?
You can easily create, delete, and update a calculated expression in a Bold BI dashboard and configure the calculated field in a widget like other, normal numeric columns as a quantitative measure.
Let’s see some calculated expression examples and how those fields are visualized to track the KPIs and metrics in corresponding domains and industries using Bold BI.
Production quality — Manufacturing
With the calculated expression given below, you can calculate the production quality KPI for the manufacturing industry, provided the quantity of production and faults are known. This expression uses the AVG function, which takes the average of one or more values (based on the grouped rows) in the referred column.
((AVG([ProductionQuantity]) — AVG([FaultCount]))/AVG([ProductionQuantity]))*100
The following image showcases this production quality KPI in a radial gauge widget of a Bold BI dashboard.
Percentage of emails delivered — Marketing
In the email marketing domain, calculating the emails-delivered percentage, also known as email delivery rate, is a key metric to determine customer engagement with an email campaign. The expression for this calculation in Bold BI should be the following.
SUM([Current Month Emails Delivered])/SUM([Current Month Emails Sent])
The Current Month Emails Delivered metric represents the emails delivered to inboxes, excluding the bounces, during the current month. This expression uses the SUM function to add one or more values (based on the grouped rows) in the referred column.
The following card in the Bold BI Dashboard showcases this KPI.
Page load time — Website traffic
With Google Analytics data, website traffic can be analyzed to ensure its good performance. Key factors such as page load time, clicks, and impressions help to determine the webpage’s performance. The following expression converts the page load time recorded by Google Analytics, which is in milliseconds by default, to seconds.
[ga:pageLoadTime]/1000
The following image showcases the page load time distribution over dates in a spline chart of Bold BI.
Average days to close an issue — GitHub project management
Tracking the closure of an issue by developer is a key metric to determine the project completion status, plan the next set of similar tasks, gauge developer efficiency, and understand task complexity. The following expression calculates the number of days it takes to close an issue with the daily activity details in a GitHub project repository. It uses the DAYDIFF function that calculates the difference between the issue closed date and issue created date.
DAYDIFF([created_at],[closed_at])
The following card showcases the average days taken to close an issue by the project team.
Production cost performance — Manufacturing
To compare the production cost in a manufacturing company between the current month and previous month, we need two different columns: one for the current month and the other for the previous month. In raw data, we used to have only one column representing values for any date range. To achieve this, the following two expressions are needed.
To calculate the manufacturing cost for the current month, use the following.
IF([Production Month]=[Current Month] AND [Production Year]=[Current Year], [ManufacturingCostPerUnit],0)
To calculate the manufacturing cost for the previous month, use the following.
IF(([Production Month]=[Last Month] AND [Production Year]=[Current Year]) OR ([Production Month]=12 AND [Production Year] = [Current Year]-1) , [ManufacturingCostPerUnit], 0)
These expressions use IF to check the condition and AND to link multiple conditions.
Here, the [Current Month] itself is an expression field whose expression will be the following.
DATEPART(“MM”,TODAY())
DATEPART extracts the month value from today’s date.
This KPI is best to showcase in a card by configuring the current month expression to actual value and the previous month value to target value sections like in the following.
Subscriber satisfaction rate — Customer service
For any Customer Services, customer satisfaction is the primary key performance indicator. In the telecommunications industry, you can track the subscriber satisfaction rate by checking the outstanding tickets to resolve. The following expression checks the status of tickets and calculates the percentage of closed ones as marking satisfied customers.
(COUNT(IF([Status]=‘Resolved’,[TicketID],null)))/(COUNT([TicketID]))
This expression uses the COUNT and IF functions where the resolved ticket IDs alone were fetched initially using the IF function. Then its total number was calculated using the COUNT function.
Purchasers — Website traffic
To count the purchasers from the purchasers list in website traffic data, aggregating the built-in purchaser ID column will be enough. If that needs to be calculated for a specific criterion, say for a particular year, which exists as a date column separately, a calculated field needs to be created. The following expression extracts the year part from a date value using the YEAR function and checks if it is a specific year (2018) or not using the IF function.
IF(YEAR([Date])=‘2018’,[PurchaserCount],0)
The following image shows the purchaser count in a card widget of a Bold BI dashboard.
Conclusion
We hope this blog provided you the benefits of using calculated fields in a dashboard. If you have any questions on this blog, please feel free to post them in the following comment section. To get started with Bold BI, please request a free 30-minute demo with our experts to discuss creating dashboards and any other features you would like to learn more about. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.
Originally published at https://www.boldbi.com on July 26, 2019.
