0 fill to current week

[Broussard_ Anna]

I have a query that returns count of issues by created week. I'd like to 0 fill to current week. The 0 fill as it exists only fills to the last week of the returned table. Is there a way to continue the 0 fill to the current week that I'm running the query? 

[Foster Cryer · Accepted solution]

Hello @Broussard_ Anna

I have an approach that may work for your situation, or at least get you going in the right direction. 

To make my example work, you need to take a couple of steps:

1. Create a dashboard and add a Calendar control where the start is whatever you want your start date to be and the end date is set to {TODAY}
   
   
2. Create a custom chart with the following SQL for the first query:
   WITH WeekOffsets AS (
   SELECT explode(sequence(0, 52)) AS n
   ),
   WeekList AS (
   SELECT
   date_add(DATE({CALENDAR.START}), n * 7) AS WeekStart,
   date_add(DATE({CALENDAR.START}), n * 7 + 6) AS WeekEnd
   FROM WeekOffsets
   )
   SELECT
   CONCAT(
   YEAR(WeekStart),
   '-W',
   LPAD(CAST(weekofyear(WeekStart) AS STRING), 2, '0')
   ) AS WeekLabel
   ,WeekStart
   ,WeekEnd
   FROM WeekList
   WHERE WeekStart <= DATE({CALENDAR.END});
   
   
3. Create a second query where you have the Created at field set to a "Week" format and have a count of the unique Issue ID fields/column
   
   
4. Join these two queries together with a left join based on the first column, which will be the week label
   
   
5. Apply a formula to the "# of Issues Created" column
   CASE WHEN "# of Issues Created" IS NULL THEN 0 ELSE "# of Issues Created" END

You may want to tweak this example a bit to make it work for you situation, but I'm hoping this will get you going in the right direction.

Thanks, and please let me know if you have any questions!

[Broussard_ Anna]

Hi @Foster Cryer , I tried out your suggestion and it worked great! It's a nifty method and I think I can use it in other things I'm trying to do. Thanks for the suggestion.

[Mohsin Shaikh]

Hello Anna,

To address your query about zero-filling to the current week in Atlassian Analytics, you can use relative date variables to dynamically update your query to include the current week. Here's a step-by-step guide with practical examples:

• Use Relative Date Variables: Utilize relative date variables such as {CURRENT_ISO_WEEK.START} and {CURRENT_ISO_WEEK.END} to define the current week in your query. This allows your query to automatically adjust to the current week whenever it is run.

• Modify Your Query: Ensure that your query includes a filter or condition that extends the date range to the current week. You can use the ADD or SUB functions with these variables to adjust the date range as needed.

• Zero Fill: After setting the date range, apply the Zero Fill feature to fill in any missing weeks with zeros. This will ensure that all weeks up to the current week are included in your results, even if there are no issues for some weeks.

Example

Suppose you want to create a chart that shows the number of issues created each week, including the current week, even if there are no issues. Here's how you can do it:

Step 1: Create a query to get the number of issues created in the current week:

• From the Jira issue table, add the Created at and Issue key columns to the “Columns” section.

• Change the aggregation of the “Created at” column to Week.

• Add the Created at column to the “Filters” section of the query and use the current week filter operator.

• Select Run query.

Step 2: Apply the Zero Fill step to fill in any missing weeks with zeros. This ensures that even if no issues were created in a particular week, it will still appear in your chart with a count of zero.

For more detailed instructions and examples, you can refer to the following resources:

• Examples for relative date variables.

• Zero Fill Example.

These documents provide comprehensive guidance on using relative date variables and zero-filling in Atlassian Analytics. I hope this information helps.

[Broussard_ Anna]

Hi @Mohsin Shaikh , thanks for the quick response. My query already has a variable date range that includes 'TODAY", but the query results stops at the last non-zero week. I tried changing the query to filter just on the current week as you suggested and I still get an empty result rather than getting the current week with 0 as the 'count' column.