Calculate values for weekdays vs weekends

Summary

It’s possible to adjust the dates of reporting in Atlassian Analytics, such as differentiating between weekends and the typical Monday-Friday business days.

In this example, we want to calculate the number of issues that were opened during the weekend per weekend day. We have a “Created At” column that holds the date the issue was created.

1. Add the Created At column to the Query twice. For the first instance of the “Created At” column, group by Day; for the second instance, group by Day of Week. Also, pull in the Issue ID and use the Uniq # aggregation to get a distinct count of issues created each day.

   

2. Add a “Filter” step that only includes “Day of Week: Created At” when the day is one of “Sat” or “Sun”.

   

3. Finally we can create a chart using this data the details what tickets were created on which weekend day over time.

   

Solution

Removing weekends from cycle time

The Atlassian Data Lake has a table, “Issue cycle time”, which returns the overall time elapsed from the start of work to the end of work. It does not take into account what are considered business days, however you can use Visual SQL steps to remove Saturdays and Sundays from your cycle time calculation.

1. Start with a visual mode query, selecting columns Work started at and Work ended at from the Issue cycle time table under “Jira family of products”. In this example, we want to find cycle time by issue, so we also add the Issue key column from the Issue table under “Jira family of products”.

2. In the “Filters” section, add two query filters where Work started at and Work ended at is not null. This ensures that only issues that had work started and completed will be returned.

   

3. Select Run query.

4. Select Formula column to add a new column to your result set, and select Custom as your formula type and using the following formula:

   1 DATEDIFF("Day of Work started at","Day of Work ended at",'day')+1

   This returns the total number of days between “Work started at” and “Work ended at”, adding 1 to be inclusive of both days.

   

5. For clarity, rename this column to “Number of days”.

6. Select Formula column to add a new column to your result set, and select Custom as your formula type and using the following formula:

   1 ((DATEDIFF("Day of Work started at","Day of Work ended at",'day')+STRFTIME('%w',"Day of Work started at"))/7)

   This calculates the number of Saturdays in between both dates using SQLite’s STRFTIMEfunction.

7. Rename this column to “Number of Saturdays”.

8. Select Formula column to add a new column to your result set, and select Custom as your formula type and using the following formula:

   1 ((DATEDIFF("Day of Work started at","Day of Work ended at",'day')+(STRFTIME('%w',"Day of Work started at")+6) % 7)/7)

   This calculates the number of Sundays in between both dates.

9. Rename this column to “Number of Sundays”.

   

10. Now that the three new columns have been added, we can calculate the number of weekdays. Do this by subtracting the number of Saturdays and Sundays from the total number of days using a Formula column and Custom formula as follows:

    1 "Number of days" - "Number of Saturdays" - "Number of Sundays"

11. Optionally, rename this new column to “Cycle time (weekdays)”.

12. Hide the “Number of days”, “Number of Saturdays”, and “Number of Sundays” columns if they are no longer needed.

    

13. That’s it! You have now generated the cycle time of each Jira issue, only counting weekdays.