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. Rather than using static date values, you can leverage Atlassian Analytics’s relative date variables to create dynamic charts whose date ranges update automatically. This is an example of a bar chart that compares Jira issues created in the current month with the previous month:

1. First, open the chart editor to create a new chart. To do this, you can either:

   1. Select Create > Chart from the global navigation

   2. Select Add chart from a dashboard’s sidebar

2. Select your data source at the top-left corner of the query. In this example, we use our Atlassian Data Lake connection.

3. From the Jira Issue table, add the Issue Id and Created At columns to the columns section of the query.

   1. Leave the aggregation as Count of distinct for “Issue Id” and the time bucket as Day for “Created At”

   2. Select "Count of distinct Jira Issue Id" in the result table to rename the column; change the name to "Previous Month" for clarity.

      

4. Now also add the Created At 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

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

      This relative date variable 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!

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

   4. Select Run query.

      
      You can transform the output data of your query by adding more Visual SQL steps after the query. For example, you can add a “Zero fill” step. 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 an “Apply formula” step to get a running total for the "Previous month" column. Select Save to save your changes. Your “Previous month” column now reflects the previous month’s running total.

   
   

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 “+ Formula column” step, select Custom as the formula type, 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 “Day of Created At” column:

   1 DATEDIFF({CURRENT_MONTH.START.SUB(1,'month')}, "Day of Created At")

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

   

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

   You can combine Steps 6 and 7 by instead using an “Apply formula” step on the “Day of Created At” column and use the same custom formula provided in Step 6. Then just rename “Day of Created At” to “Day #”.

8. Use the “Reorder columns” step to move Day #so it’s the leftmost column in the result table.

9. Now you need to bring in data for the current month. Select Add query > Copy of Query 1 to quickly make a clone of the first query. You 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 “Copy of Query 1”, edit the starting and ending dates for the “Created At” filter to {CURRENT_MONTH.START} and {CURRENT_MONTH.END}, respectively.

   2. Select Run query.

   3. Select "Previous Month" in the result table to rename the column; change the name to "Current month" for clarity.

   4. Select Run query again.

      

   5. Use an “Apply formula” step to get the running total for the “Current month” column.

   6. Add a “+ Formula column” step, select Custom as the formula type, then use the following formula:

      1 DATEDIFF({CURRENT_MONTH.START}, "Day of Created At")

   7. Rename this new column to "Day #".

   8. Hide the "Day of Created At" column.

   9. Move the "Day #" column so that it’s the leftmost column in the result table.

      

10. Select the “Join” step that was added after you created the second query to view the post-merge result set. By default, the two result sets are merged with an outer join, which is what we want for this example.

11. Select Bar as the chart type and update the bar chart settings by selecting the gear icon above the chart preview. In the General tab of the chart settings, deselect "Stacked" to have separate bars for the previous and current month’s data. Feel free to change the other settings too!