Calculated Date Ranges

Using calculated date fields and gremlin to return a date range automatically

Kristine Marhilevica avatar
Written by Kristine Marhilevica
Updated over a week ago

The calculated date range is not a field option when selecting a field type in Ardoq, but there is a workaround that uses 2 calculated date time fields to join together into a date range field.

Disclaimer: The following instructions are provided as a workaround and may not be effective in all scenarios. While we strive to offer helpful solutions, we cannot guarantee a 100% success rate due to the variability of environments and configurations. Please proceed with caution and at your own discretion.

1. Create Your Calculated State Date Field

When you create your field name, make sure to name it <Field_Name>.StartDate. This will trick Ardoq into thinking that this is the start date to a range field.

2. Put In Your Start Date Gremlin Query

This can be modified to support any incoming or outgoing reference type

get referenced component date fields 
\this gets the min value of the start dates

g.V(ids). where('Realizes').has('active_period_start_date')). project('id', 'name', 'value').
by('Realizes').has('active_period_start_date').order(). by('active_period_start_date', desc).limit(1).

3. Create Your Calculated End Date Field

Similar to the start date, when you create your field name, make sure to name it <Field_Name>.EndDate.

4. Put In Your End Date Gremlin Query

this one gets the max value of the end dates 
g.V(ids).where('Realizes').has('active_period_end_date')). project('id', 'name', 'value').
by('Realizes').has('active_period_end_date').order(). by('active_period_end_date', desc).limit(1). values('active_period_end_date'))

5. Save and Apply

Once saved and applied Ardoq will then join them into a single calculated date range field. The start and end date calculations will show separately in the workspace.

Once Ardoq joins the fields you cannot edit the scripts anymore. So if you change the queries, you have to delete the fields and repeat the process.

Did this answer your question?