Filter specific days of the week
Platform Notice: Cloud Only - This article only applies to Atlassian products on the cloud platform.
Say you want to filter out weekends, or your business days comprise of a Monday-Saturday week or a Monday-Thursday week. How do we filter these other days of the week out?
This can be done in through Visual SQL steps. Visual SQL steps use SQLite so we can employ functions available in SQLite.
Our first step is our query. We’ll need to add our date column to the query of our chart and select “Day” as the time bucket for the date column. Let’s use this query as an example:
Next, we can add a "Formula column"step. We’ll use Custom as the formula type and use the following formula:
1
STRFTIME('%w', "Day of Created At")
Once we apply this “Formula column” step, we’ll have our days of the week listed out with Sunday = 0, Monday = 1, Tuesday = 2, …, Saturday = 6.
We’ll rename the new column to “DOW” (i.e., Day of Week) for clarity.
Now we can filter out values 0 and 6 using a “Filter” step to remove any rows where Created At is a weekend. We’ll exclude rows matching all conditions where DOW is one of 0 and 6 as done below:
And that’s it! We’ve now excluded all the Saturday and Sunday data. You can now hide the DOW column using a"Hide column"step if it’s no longer needed.
Was this helpful?