How to create a time series dataset

[nclark]

Hi folks, just wondering if you have any advice for creating a time series dataset that's based on issue_history table values. e.g., let's say i wanted to record the count of priority values of open issues over time (assuming priority field values do change over time). Let's suppose we want to record the priority of all open issues as of december 1st, jan 1, feb 1, march 1. Any suggestions on how to do this? I believe i need to use the 'started at' and 'ended at' fields, but im not sure how to iterate over each date (dec 1, jan 1, feb 1, march 1, etc.), other than a big copy/paste job

[Jessie Turpin]

Hey @nclx, thanks for the question! In the past when I tinkered with creating a chart calculating the number of open issues each month, here is a high-level overview of how I approached it: 

• Query 1 - I used a SQL query (below) to display all of the months in the time range I was interested in. I used a Calendar control, but you could hard-code the dates too. 

  • WITH dates AS (SELECT sequence(to_date({CALENDAR.START}), to_date({CALENDAR.END}), interval 1 months) AS months)
    
    SELECT explode(months) from dates

• Query 2 - Queries the issue keys and the minimum Started at date from the Issue status history table for any categories where the Status category isn't "Done" (aka first time work started on an issue)
• Query 3 - Queries the the issue keys and the date the issue moved to Done (aka when the work ended). 
• Queries 2 & 3 are Outer joined together using the Issue key column.
• The merged results of Queries 2 & 3 are then Cross joined with Query 1 to associate each issue with each of the months during the time range (this helps when an issue is open longer than 1 month). 
• I added a Case statement (below) to show 1 for any month(s) when an issue was open based on the Started at date from Query 2 and the Ended at date from Query 3.   

  • case when "In progress start" <= "months" and "months" <= "Completed date" then 1 when "In progress start" is null and "months" <= "Completed date" then 1 end

• Then the rest of the steps are filtering out issues that weren't open during the date range, counting the number of open issues per month, hiding and renaming columns.

You'd need to do a few extra steps to also have the priority change counts included too, but you'll likely need multiple queries to fully capture the historical number of issues open in a given month. Let us know if you run into issues or if you have other questions!