Use a calendar control to filter on start date

[Mikael Sandberg]

Hi and happy Friday!

Is there a way to filter start date based on a calendar control and if so, how do you do that?

[Skyler Ataide · Accepted solution]

Hey @Mikael Sandberg! 

Yes it is possible to filter start date based on a calendar control. The first step is to create your calendar control and save it to your dashboard. Once the calendar control is saved to the dashboard, you can connect it your your chart in the "Filters" section of a visual mode query:

1. In the “Filters” section of your visual mode query:

   1. Add the date column you want the control to filter, in this case the start date column.

   2. Select the filter operator. Most people use between and including.

   3. Reference your control using its dashboard variable:

      • If the “Data type” of your control is Date, use {CALENDAR_NAME}

      • If the “Data type” of your control is Date range, use {CALENDAR_NAME.START} and {CALENDAR_NAME.END} for the start and end dates, respectively.

      • Replace CALENDAR_NAME with the name of your control.

2. Select Run query. The result table will update with the new filter applied to the data.

In my example below, I have a calendar control named 'CALENDAR', and I am using the between and including operator to filter on the start date column for dates that are in the range of my calendar control: 

Hope this makes sense! Let me know if you have any questions :) 

[Skyler Ataide]

For more information, here is our documentation on setting up a calendar control: https://support.atlassian.com/analytics/docs/set-up-a-calendar-control/ 

[Mikael Sandberg]

The start date I am looking for is the custom field Start date that Jira creates, not the start date on the version which I believe is the one you are using above.

Start date from the Project fix version table is empty in this JSM project since it is not using versions:

[Skyler Ataide]

I see, thank you for the clarification! Yes this should also be doable using a Visual SQL "Filter" step. Jira issue custom fields are store in the Issue Field table. Here are the steps to filter on the custom field start dates using a calendar control: 

1. First, write a query that brings in the Jira issues and their respective issue field values. To only get values for the start date field, include a filter in your visual mode query to filter where the "Name" column from the "Issue field" table is equal to 'Start date' 
2. Next, use a "Filter" step to filter your results by the calendar control. You can use the syntax {CALENDAR_NAME.START} and {CALENDAR_NAME.END} in the filter step to connect the calendar control; where "CALENDAR_NAME" is replaced with the name of the calendar control on your dashboard: 
   
   

[Mikael Sandberg]

Awesome, thank you so much @Skyler Ataide!