Hello Community,

I'm stuck on writing a formula to stop my metric double counting the last update time stamp for a custom field that I have.

The metric I have to calculate the hours worked per ticket from the custom fields is:

"IF (D_COUNT(Updates)>1)
THEN ((SUM(NULL VALUE TOTAL 1) + SUM(NULL VALUE TOTAL 2) + SUM(NULL VALUE TOTAL 3) + SUM(NULL VALUE TOTAL 4) + SUM(NULL VALUE TOTAL 5) + SUM(NULL VALUE TOTAL 6) + SUM(NULL VALUE TOTAL 7) + SUM(NULL VALUE TOTAL 8) + SUM(NULL VALUE TOTAL 9) + SUM(NULL VALUE TOTAL 10)) / D_COUNT(Updates))
ELSE (SUM(NULL VALUE TOTAL 1) + SUM(NULL VALUE TOTAL 2) + SUM(NULL VALUE TOTAL 3) + SUM(NULL VALUE TOTAL 4) + SUM(NULL VALUE TOTAL 5) + SUM(NULL VALUE TOTAL 6) + SUM(NULL VALUE TOTAL 7) + SUM(NULL VALUE TOTAL 8) + SUM(NULL VALUE TOTAL 9) + SUM(NULL VALUE TOTAL 10))
ENDIF"

If there is more than 1 update then the formula will divide the total hours by the number of updates per month otherwise it will double/triple/quadruple the calculated metric hours.

I have also added this calculated attribute for finding out the last time stamp update for the custom field is:

DATE_LAST_FIX([Update - Timestamp], [Update ticket ID])

This accurately pulls the last update on my customer field "Number of active engineers" as this can be updated multiple times before the ticket is solved.

As you can see on the screenshot above, if the "Number of active engineers" per ticket is updated multiple times over a few months it will count the total hours for each month, essentially duplicating it.

So essentially what i'd like the query to do is only count the numbers once on the last update of the custom field "Active numbers of Engineers" and then only show that number in the correct monthly column.

I tried with ChatGPT but it didn't really work to be honest.