Creating Custom Line Charts

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 QQL Console 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 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.

Interaction Rate - Automotive - 10-19-2016 to 11-02-2016.jpg

Here, we are starting off with the Facebook Interaction Rate metric. The current QQL Code 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 likes. All we need to do is delete ‘ownPostsLikes’ from the query.

Your new QQL Code should look as follows:

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.

Custom Interaction Rate - Automotive - 10-19-2016 to 11-02-2016 (1).jpg

Here is our new line chart. As you can see, the graph tells a very different story now that we are only looking at comment and share interactions and taking away the likes.

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:

{
 "config": {
     "qqlQuery": "SELECT profileId AS dim1, time AS dim2, ZEROIFNULL(((ownPostsComments+ownPostsShares*1.0)/ownPosts)/((fansBefore+fans)/2)*100) FROM facebook",
     "metaData": {
       "dim1": {
             "regressionLine": true,
             "regressionName": “Best Fit”
       }
     }
   }
 }

We are using the basic regression line and naming it ‘Best Fit’. This is the resulting chart:

Custom Interaction Rate W- Regression - Mercedes-Benz - 10-19-2016 to 11-02-2016.jpg

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 can only analyze 1 profile at a time.

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 a profile over time.

SELECT “Images Likes” AS dim1, time AS dim2, imagesLikes FROM instagram UNION SELECT “Video Likes” AS dim1, time AS dim2, videoLikes FROM instagram

Images Vs Videos Likes - Victoriassecret (Victoria's Secret) - 10-19-2016 to 11-02-2016.jpg

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 Y-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:

{
 "config": {
  "qqlQuery": "SELECT 'Images Likes' AS dim1, time AS dim2, imagesLikes FROM instagram UNION SELECT 'Videos Likes' AS dim1, time AS dim2, videosLikes FROM instagram UNION SELECT 'Images Comments' AS dim1, time AS dim2, imagesComments FROM instagram UNION SELECT 'Videos Comments' AS dim1, time AS dim2, videosComments FROM instagram",
   "metaData": {
    "dim1": {
      "axes": [
    {
      "labels": [
        "imagesLikes",
        "videosLikes"
      ]
    },
    {
      "labels": [
        "imagesComments",
        "VideosComments"
      ]
    }
   ]
   }
  }
 }
}

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).

Images Vs Videos Likes & Comments (2 Y-Axis) - Victoriassecret (Victoria's Secret) - 10-19-2016 to 11-02-2016 (1).jpg

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.

Stay tuned for more QQL articles about advanced metadata and some of our other visualization types!

Still need help? Contact Us Contact Us