With over 130 line chart default metrics within quintly, it is our most common visualization type. Line charts are great when you want to visualize continuous data and identify trends in it. We often use line charts to look at data over time (time being in the x-axis), but other continuous variables will also work. Our guide on using the Metric Builder is recommended before moving on.
In this article, we’ll start off with how to create a basic line chart. Then, we’ll go more in depth and look at some advanced line charts: regression lines, multi-metric, & secondary axes.
Basic line charts
Showing metrics like Fans Total count and Interaction Rate over time is a popular use case for line charts. It is also super easy to edit a default metric line chart to show the data you are most interested in. Let’s look at an example.
Here, we are starting off with the Facebook Interaction Rate metric. The current QQL Query is as follows:
SELECT profileId AS dim1, time AS dim2, ZEROIFNULL((((ownPostsLikes+ownPostsLove+ownPostsWow+ownPostsHaha+ownPostsSad+ownPostsAngry+ownPostsComments+ownPostsShares*1.0)/ownPosts)/((fansBefore+fans)/2)*100) FROM facebook
Now, let’s say we only want to include comments and shares and remove reactions. All we need to do is delete the reactions fields from the query:
SELECT profileId AS dim1, time AS dim2, ZEROIFNULL(((ownPostsComments+ownPostsShares*1.0)/ownPosts)/((fansBefore+fans)/2)*100) FROM facebook
Click on “Run QQL”. Once it has run, make sure to save your new metric.
Here is our new line chart. As you can see, the graph tells a different story now that we are only looking at comment and share interactions and taking away the reactions.
Adding a regression line to a line chart
It is possible to add a regression line, or “line of best fit” to your basic line chart. One important thing to note is that in order for the line to show, you can only have 1 profile selected. There are several variations of metadata you can use when adding a regression line.
To add a regression line, you need to use one of the “regressionLine” options below. “regressionName” and “period” are optional.
“regressionLine” = true
Adds a basic regression line
“regressionLine” = “sma”
Adds a regression line using Simple Moving Average
“regressionLine” = “ema”
Adds a regression line using Exponential Moving Average
“regressionName” = “Name” (optional)
Specifies the name of the line you will see in the legend. If you do not specify a name in the metadata, the regression line will automatically be named ‘Linear Trendline’.
“period” = 3 (optional)
This value describes the smoothness of the line. The higher this value the smoother the line.
Note: The line for the moving averages has no data for the first days, depending on the chosen period. If you choose a period of 3, you won’t see data for the first 3 days. This is because the moving average needs more data points to smooth the line. For example: If we have a line chart with 30 data points, the moving average takes the 30 data points to create a smoothed line with 27 data points.
Let’s take our example from the basic line chart. The QQL Code will remain unchanged. However, we are now going to need to add some metadata. I will also be selecting only the Mercedes-Benz profile, as the trendline only works when we analyze a single profile.
Here is what the config will look like with our added regression line metadata:
"qqlQuery": "SELECT profileId AS dim1, time AS dim2, ZEROIFNULL(((ownPostsComments+ownPostsShares*1.0)/ownPosts)/((fansBefore+fans)/2)*100) FROM facebook",
"regressionName": “Best Fit”
We are using the basic regression line and naming it ‘Best Fit’. This is the resulting chart:
Creating line charts that display multiple metrics
It is possible to analyze more than 1 metric on a single line chart. However when we do this, we have to aggregate the data in order for it to work when more than 1 profile is selected.
We can use UNION queries to create a line for each metric we want. dim1 would be a string of the metric that line represents. dim2 is still time, and then there’s the column. This is followed by UNION and another query that calls the next metric you want to analyze. You just need to add a UNION query for every metric/line you want to add.
Here is an example of a line chart that compares the photo likes and video likes of an Instagram account over time:
SELECT “Images Likes” AS dim1, time AS dim2, SUM(imagesLikes) FROM instagram GROUP BY dim1, dim2 UNION SELECT “Videos Likes” AS dim1, time AS dim2, SUM(videosLikes) FROM instagram GROUP BY dim1, dim2
As we used SUM in the query, that means if more than 1 Instagram account is selected, their values will be summed for each day. However we could have also done AVG, which then would have averaged their values for each day.
If, for example, we wanted to also compare these to the comments on images and videos, we could simply add 2 more UNION queries.
Adding a secondary axis to a multi-metric line chart
If you want to analyze more than 1 metric for the profile but the values differ greatly between the metrics, using a secondary Y-Axis could be useful. The query is written as the same as our example above, but we’ve also added comments on images and videos. Also, there’s now an additional step of changing the metadata to allow for a secondary Y-Axis.
You write the query the same as you did in the example above, then add 2 union queries for the comments columns.
Here is the full config for our example metric:
"qqlQuery": "SELECT 'Images Likes' AS dim1, time AS dim2, SUM(imagesLikes) AS imagesLikes, FROM instagram GROUP BY dim1, dim2 UNION SELECT 'Videos Likes' AS dim1, time AS dim2, SUM(videosLikes) AS videosLikes, FROM instagram GROUP BY dim1, dim2 UNION SELECT 'Images Comments' AS dim1, time AS dim2, SUM(imagesComments) AS imagesComments FROM instagram GROUP BY dim1, dim2 UNION SELECT 'Videos Comments' AS dim1, time AS dim2, SUM(videosComments) AS videosComments FROM instagram GROUP BY dim1, dim2",
This metadata puts the likes on images and videos on the primary Y-Axis (to the left) and the comments on images and videos on the secondary Y-Axis (to the right).
Line charts are a simple yet incredibly effective visualization for analyzing continuous data. If you are just starting out with QQL, creating custom line charts is a great way to practice.