Build a Dynamic Bar Chart to Compare Time Periods

Charts

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

To do this yourself,

  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

  1. 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.
  2. Drag the Visitor Id column into the Measures drop zone.
  3. 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.
  4. 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

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

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

    Run query

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

  8. 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.
  9. 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.
  10. 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.
  11. 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

  12. 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.
  13. 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

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

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

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

  17. 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.
  18. 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

    optimzie chart settings

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


Related Charts Help Articles

See more