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:
CASE
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
END
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:
(COUNT(CASE
WHEN (EXTRACT(EPOCH FROM (checkin_datetime - arrival_time)) / 60) < 0 THEN 1
ELSE NULL
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.