Skip to main content
Calculated metrics & columns

Learn how to create calculated metrics & columns

Erik avatar
Written by Erik
Updated over a week ago

Mapsly's Analytics offers a wide variety of ways to use metrics and calculated columns, many of which are designed to save you time and effort when querying charts. This article discusses examples of using calculated metrics and columns by using custom SQL.

Using calculated columns to display custom values

In order to display custom values in charts based on certain values of other columns, you may want to create a calculated column.

To create a calculated column, click the "Edit" button for the dataset where you would like to add it:

Switch to the "Calculated Columns" tab:

In this example, we will create a "distance_from_location" calculated column that will assign different values based on the check_distance column values using the following SQL query:

WHEN checkin_distance BETWEEN 0 AND 0.500 THEN 'Verified'
WHEN checkin_distance > 0.500 AND checkin_distance <= 1 THEN '500-1000 m'
WHEN checkin_distance > 1 AND checkin_distance <= 5 THEN '1-5 km'
WHEN checkin_distance > 5 AND checkin_distance <= 10 THEN '5-10 km'
WHEN checkin_distance > 10 THEN '10+ km'
ELSE 'Unknown' -- This is optional and can be adjusted based on your needs

As a result, we will be able to view distances with the following values: "Verified", "500-1000 m", "1-5 km", "5-10 km" and "10+ km":

Using calculated metrics to display a percentage value

In order to display a ratio of one metric over another metric as a percentage over a period of time, you may want to create a calculated metric. In this example, we will add an in-place custom SQL query that will calculate the ratio of visits completed on time over the total number of visits:

Below is the query used for this metric:

WHEN (EXTRACT(EPOCH FROM (checkin_datetime - arrival_time)) / 60) < 0 THEN 1
END)) * 100 / COUNT(id)

As we can see, this allowed us to view the percentage of visits completed on time over a period of time:

Contact Mapsly support at any time

If you have any questions or need help configuring your charts & dashboards, contact Mapsly support team 24/7 by chat or email at [email protected], we'll be happy to help.

Did this answer your question?