Documentation Charts FAQs

Build a dynamic Bar chart to compare time periods

It can be helpful to create a chart that monitors a metric’s growth in comparison to something else. This is an example of a Bar chart that compares visitors in the current month with the previous month:

Bar Chart

Here’s how you can do it:

In the Visual SQL interface

  1. Create a new chart by clicking Explore from the top nav bar.

  2. Select your data source at the top-left corner of the Query. In this example, we use our SaaS Company Demo Data data source, which every Chartio user has by default.

    If you don’t see SaaS Company Demo Data in your data source dropdown but would like to use it, you can add it as a new data source by choosing SaaS Company Demo Data at the bottom of the page.

  3. From the Visitors table, add the Visitor Id and Created Date columns to the Columns section of the Query. By default, Chartio uses the Day time bucket for date columns, which is what we want for this particular example.

  4. Now also add the Created Date column to the Filters section of the Query. We want to make sure to use the same date column for bucketing and filtering our metric. 1. For the filter operator, use between and including
    1. For the starting date of your filter range, use {CURRENT_MONTH.START.SUB(1, 'month')}.

      This sets the current date of the filter to the first day of the previous month. To explain the code a bit more, it starts with the current month, goes to the first day of the current month, then subtracts one month—giving the first day of the previous month!

    2. For the ending date of your filter range, use {CURRENT_MONTH.END.SUB(1, 'month')}.

      This is similar to how we got the starting date, but instead looks at the last day of the month.

    Use columns from the Visitors table to create the first Query

    Note: You can transform the output data of your query by adding a few steps in the Pipeline. For instance, you can add a Zero Fill step to the Pipeline. In our example, this shouldn’t change anything, but it’s good to have this step in case you have any gaps in your data.

  5. Use Edit Formula to get a Running Total of the Visitor Id column. Click Save. Your Visitor Id column now reflects the previous month’s running total. For clarity, let’s rename this running total column to Previous Month.

  6. In order to compare the count on the first day of the previous month to the first day of the current month, we need to add another column. Add a Calculated Column and select Custom formula. Then use the following formula to calculate the difference between the first day of the previous month and whatever date is in each row of the “Created Date” column:

    datediff({CURRENT_MONTH.START.SUB(1,'month')}, "Created Date")
    

    For clarity, let’s also rename this column to Day #.

  7. Hide the Created Date column. Since we’ll compare using the Day #, we don’t need the “Created Date” column anymore.

    Tip! You can combine Steps 6 and 7 by instead using an Edit Formula Action on the Created Date column and use the same Custom formula provided in Step 6. Then just rename Created Date to Day #.

  8. Use the Reorder Columns or Move to Start Action to move Day # to the far left of the Result Table.

  9. In order to compare the previous month’s data to the current month, we need to create a second query by clicking Add Query and pull the current month’s data. We need to apply the same transformations (Steps 2 - 8) to the second query (with a few adjustments) to properly merge the two queries, which we can do before we merge them:

    1. For Query 2, add the same Visitor Id and Created Date columns from the Visitors table into the Columns section.
    2. Add the Created Date column to the Filters section and use the same filter operator we used for Query 1: between and including. But for the starting and ending dates, use the following instead: {CURRENT_MONTH.START} and {CURRENT_MONTH.END}, respectively.

    1. Use Edit Formula to get the running total for the Visitor Id column then rename it to Current Month.
    2. Add a Calculated Column, select Custom formula, then use the following formula:

      datediff({CURRENT_MONTH.START}, "Day of Created Date")
      

      Rename this new column to Day #.

    3. Hide the Created Date column.
    4. Move the Day # to the far left of the Result Table.

  10. Now that Query 2 and all the transformation Actions are set, ensure that the last step in the Pipeline for Query 1 is selected then click Merge Queries. By default, the two result sets are merged with an Outer Join, which is what we want for this example.

  11. Select the Bar chart type and update the Bar chart settings by clicking the Paintbrush. Deselect Unstacked from the General tab to have separate bars for the previous and current month’s data. Feel free to change the other settings too!

In the Data Explorer interface

  1. Create a new chart on Chartio by clicking Explore in the top bar.
  2. Select your data source in the top-left corner of the Chart Creator. We will use the Chartio Demo data source that every Chartio user has by default. Note: If you don’t see Chartio Demo in the data source dropdown of choice but you would like to use it, add it by clicking Data Sources in the top bar, then Add a Data Source, and choose one of the data sources under Chartio Demo Data at the bottom.

    Selecting data source

  3. Expand the Visitors table (the metric we chose to focus on) in the list of source tables by clicking on it. This reveals the available columns of data in this table.
  4. Drag the Visitor Id column into the Measures drop zone.
  5. Drag and drop the Created Date column into the Dimensions drop zone. By default, Chartio will use the date column to bucket our data by days, which is what we want for this particular example.
  6. Grab the Created Date column again from the source list, this time dropping it into the Filters drop zone. We want to use the same date column for bucketing and filtering our metric. Note: A dropdown menu will automatically open when you place your date column into the Filter field. To open these menus at anytime, click any of the light blue columns in the fields.

    Add filters

  7. In the dropdown menu for the filter date column, switch the selector from “earlier than” to “between and including.” You should now have an input for the starting and ending dates of your filter range. Instead of dates, type SQLite code into these inputs to create a dynamic range.

    a. Start by typing {CURRENT_MONTH.START.SUB(1, ‘month’)} into the top input. This will set the current date of our filter to the first day of the previous month. The code starts with the current month, goes to the start date, and then subtracts one month.

    b. Type {CURRENT_MONTH.END.SUB(1, ‘month’)} in the second input to set the end date of your filter to the end date of the previous month.

  8. Click Ok to close the dropdown and click Run Query.

    Run query

  9. You can change the label of the measures for clarity. Click Count of distinct Visitors column in the Measures drop zone to open its menu and relabel this column to “Previous month” and click Ok.

    Measures of data

  10. You can transform the output data of your query by adding a few steps in the Data Pipeline. Add a Zero Fill step to the pipeline by clicking the Add Transformation button below the query window, and then Zero Fill Data (under the Transform tab). In our example, this shouldn’t change anything, but it’s good to have this step in case you have any gaps in your data.
  11. Add an Edit Column step to the pipeline by clicking the plus sign under the Zero Fill Data step and choosing Edit Column (under the Columns tab). Choose your measure column (which should be named “Previous month”) for the column. Choose Running total for the Formula Type (it’s the default). Click Apply and Close. The numbers in the chart table now reflect a running total.
  12. In order to compare the count on the first day of the previous month with the first day of the current month, add a third day # column to the table.
    • Create an Add Column step in the pipeline by clicking the plus sign and then choosing Add Column from under the Columns tab.
    • Name the new column “Day #.”
    • Choose the “Custom Formula” Formula Type.
    • Enter datediff({CURRENT_MONTH.START.SUB(1,’month’)}, “Day of Created Date”) into the Formula field. This will calculate the difference between the first day of the previous month, and whatever date is in each row of the “Day of Created Date” column.
    • Click Apply and Close.
  13. Add a Hide Columns step to the pipeline by clicking the plus sign and then choosing Hide Columns.
    • Choose the Day of Created Date column and click Apply and Close. Since we’ll be comparing this to the day number, we won’t need this.

      Transform data

  14. Add a Reorder Columns to the Pipeline.
    • Reorder the columns so the Day # column comes first by dragging and dropping Day # above Previous month and then click Apply and Close.
  15. In order to blend the two tables in order to compare them, clone the layer you’ve been working on by opening the dropdown menu above the data pipeline by clicking the downwards arrow next to Dataset 1 and clicking Clone.

    Clone dataset

  16. Click the Previous month column in the Measures zone of this new layer to open its name and rename it to “Current month.”

    Measures on Chartio

  17. Click the Created Date column in the Filter zone to open its menu. Delete the .SUB(1, ‘month’) part from each of the two range inputs. The start and end fields should read {CURRENT_MONTH.START} and {CURRENT_MONTH.END}. Click Ok.

    Add date filters

  18. You will get an error when you click Run Query, but we can fix that by going to the Edit Column step and clicking the Edit button. Then change the column to the Current month column.

    Fixing errors

  19. Under Add Column, get rid of the .SUB(1, ‘month’) portion of this formula as well. The final formula should be datediff({CURRENT_MONTH.START}, “Day of Created Date”). Click Apply and Close.
  20. Switch to the bar chart visualization type by clicking the button below the final chart preview. Click on the Settings button with the gear above above the chart preview. Uncheck the box labeled Stacked. Click Done.

    Dynamic bar chart

    Optimize chart settings

    Under Settings, you can switch to the Color tab to change the colors of the chart.