[Closed] What are the earliest and latest date functions?

In this table, you'll find a list of each earliest and latest date function and how to use it:

In this example you'll use the DATE_LAST function to create a query that returns tickets solved on the last date, then by adding year, month and group attributes to the query you will get resolutions for the last date of the month for each group.

To create the query

1. Create a new query using the Support: Tickets dataset.
2. Create a standard calculated metric named Tickets solved on last date with the following formula:

   IF DATE_LAST([Ticket solved - Date])=[Ticket solved - Date] THEN [Ticket ID] ENDIF

3. Edit the metric you just created and set its default aggregator to COUNT you can also remove other aggregators.
4. Add the Tickets and Tickets solved on last date metric to the Metrics panel of query builder.
5. In the Rows panel, add the Ticket solved - Year and Ticket solved - Month attributes. You’ll see total number of solved tickets and tickets solved on the last day of the month, for example:

   

6. In the Rows panel, add the Ticket group attribute. You’ll see that the tickets solved on the last date are determined per group in addition to year and month:

   

In this example you'll use the DATE_FIRST function to create a query that returns the earliest ticket assignment timestamp. Then, you'll add the assignee and date attributes to get the earliest timestamp per date and assignee.

To create the query

1. Create a new query using the Support: Tickets dataset.
2. Create a standard calculated attribute named Earliest assignment - timestamp with the following formula:

   IF DATE_FIRST([Ticket assigned - Timestamp])=[Ticket assigned - Timestamp] 
   THEN [Ticket assigned - Timestamp] ENDIF

3. Add the Tickets metric to the Metrics panel of query builder.
4. In the Rows panel, add the Assignee name and Ticket assigned - Date attributes. Configure the second attribute to show tickets created this week.
5. In the Rows panel, add the Earliest assignment - timestamp attribute, then filter it by excluding NULL values. You’ll see the earliest daily ticket assignments by assignee and date, for example:

   

Tickets can be resolved and reopened multiple times so in some cases it is useful to look only at the latest resolutions. In this example you'll use the DATE_LAST_FIX function to produce a metric that will return the number of final ticket resolutions.

To create the query

1. Create a new query using the Support: Ticket updates dataset.
2. Create a standard calculated metric named Final resolutions with the following formula:

   IF ([Changes - Field name]="status"    
          AND [Changes - Previous value]!="solved"
         AND ([Changes - New value]="solved" OR [Changes - New value]="closed")  
         AND  DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name], [Changes - New value])=[Update - Timestamp]) 
   THEN [Update ID] 
   ENDIF

3. Edit the metric you just have created and set its default aggregator to COUNT. You can also remove other aggregators.
4. Add the Resolutions and Final resolutions metrics to the Metrics panel of query builder.
5. In the Filters panel, add the Update - Date attribute and configure this to show updates in the last 30 days. Your query will return the total number of resolutions and number of final resolutions. You can slice these metrics using any attributes, for example Update ticket group.
   
   

In this example you'll use the DATE_LAST_FIX function to create an attribute that will return the latest agent comment timestamps that will not be sliced by the attributes from the query. This query can help you detect agent seats that were not used for a long time.

To create the query

1. Create a new query using the Support: Ticket updates dataset.
2. Create a standard calculated attribute named Latest agent comment - timestamp with the following formula and enable the Sort like time attribute setting:

   IF ([Comment present]=TRUE 
   AND [Updater role]!="End-user" 
   AND DATE_LAST_FIX([Update - Timestamp],[Updater name],[Updater role],[Comment present])=[Update - Timestamp]) 
   THEN [Update - Timestamp] ENDIF

3. Add the Comments metric to the Metrics panel of query builder.
4. In the Filters panel, add the Update - Date attribute and configure this to show updates in the last year.
5. In the Filters panel, add the Updater role attribute and configure this to show Agents and Admins.
6. In the Rows panel, add the Updater name attribute.
7. Finally, in the Rows panel, add the Latest agent comment - timestamp attribute, then filter it to exclude NULL values. You’ll see the list of agents and their latest comment timestamps:

   

One of the default metrics available in the Tickets dataset is First reply time. It gives you insight on how long it took for an agent to respond to the end user. Some customers have internal processes where an internal comment is added to the ticket before a public reply. In this example you'll use the DATE_LAST_FIX function to create a query that will return the average first internal comment time by month.

To create the query

1. Create a new query using the Support: Ticket updates dataset.
2. Create a standard calculated attribute named First internal comment - Timestamp with the following formula and enable the Sort like time attribute setting:

   IF ([Comment present]=TRUE
   AND [Comment public]=FALSE
   AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public])=[Update - Timestamp])
   THEN [Update - Timestamp] ENDIF

3. Create a standard calculated metric named First internal comment time (hrs) with the following formula:

   DATE_DIFF([First internal comment - Timestamp], [Ticket created - Timestamp], "nb_of_hours")

4. Edit the metric you just created, set it's default aggregator to AVG, display format to Custom, and set Decimal points to 1 and Suffix to hrs
5. Add the First internal comment time (hrs) metric to the Metrics panel of query builder.
6. In the Columns panel, add the Ticket created - Month attribute and configure this to show updates in the last 6 months.
7. Finally, in the Visualization types menu, select the Column chart. You’ll see the first internal comment time by month:

   

More information

For more information, see the following resources:

• Aggregation level, earliest and latest date functions EAP community
• Sign up for the EAP