All CollectionsData VisualizationViews
How to Automatically Calculate Component End Dates and Create a Quarter-Based Roadmap
How to Automatically Calculate Component End Dates and Create a Quarter-Based Roadmap

Master quarter-based project roadmaps with Gremlin Query Language for improved planning.

Written by Kristine Marhilevica
Updated over a week ago

In this tutorial, we will guide you on how to automatically calculate component end dates and transpose them onto a calculated list field, displaying them in a quarter-based format (e.g., Q1 2020, Q2 2020, etc.). This method can be applied to various areas, such as applications, initiatives, and technology. By following these steps, you will be able to create a roadmap for any component with a date range outside of the timeline view easily and without manual input.

Step-by-Step Guide:

1. Create a Calculated List Field:

1. Open your workspace which has components with the end dates.

2. Create a new calculated list field (we will refer to this as "Quarter-Year" field).

2. Prepare the Quarter-Year Data in Excel:

1. Open Microsoft Excel or a similar spreadsheet program.

2. In the first column, create a list of records ranging from Q1 2020 to Q4 2030 (you can adjust the range according to your needs).

3. Select all the cells containing the quarter-year values, copy them to your clipboard.

3. Import the Quarter-Year Data to the Calculated List Field:

2. Select the "Quarter-Year" calculated list field.

3. Paste the quarter-year values from your clipboard into the field (you can paste all values at once, not one by one).

4. Implement the Gremlin Query for Automatic Calculation:

1. In your Ardoq instance, click Create & edit query.

2. Copy the provided Gremlin Query below:

`getYear = {return it.split('-')[0]}getMonth = {return it.split('-')[1]}getQuarter = {  month = getMonth(it)  if(month == '01' || month == '02' || month == '03')    return 'Q1';  if(month == '04' || month == '05' || month == '06')    return 'Q2';  if(month == '07' || month == '08' || month == '09')    return 'Q3';  if(month == '10' || month == '11' || month == '12')    return 'Q4';  }g.V(ids).  has('live_end_date').  project('id', 'name', 'value').    by(id).    by('name').    by(values('live_end_date').map{getQuarter(it.get()) + ', ' + getYear(it.get())})`
3. Paste the Gremlin Query into the Gremlin Query Engine and execute it. This will automatically calculate the component end dates and assign them to the corresponding quarter-year values in the "Quarter-Year" calculated list field.

5. Verify the Results in Component Matrix View:

1. Go to the Component Matrix view in Ardoq.

2. Check if the calculated "Quarter-Year" field has been populated with the correct quarter-year values.

3. If the quarter-year values have been assigned correctly, proceed to configure the Component Matrix view by adding the "Quarter-Year" field as a column.

4. Adjust any additional settings in the Component Matrix view to suit your needs, such as sorting, grouping, or applying conditional formatting based on the quarter-year values.

6. Share the Roadmap with Stakeholders:

1. Once you have successfully configured the Component Matrix view with the "Quarter-Year" field, you can now share the roadmap with stakeholders.

2. Add the Component Matrix view to a Presentation to share with your team or stakeholders.

3. Use the roadmap to facilitate discussions around project timelines, resource allocation, and strategic planning.

These steps should help you create an automatically calculated, quarter-based roadmap for your project components. The roadmap can help you easily keep track of end dates without manual input and have a clear overview of your project's progress across various areas like applications, initiatives, and technology.