Calculate duration from start & end items

Hello guys,

We’re logging every start and end of processes that are running in our systems, and we’re trying to retrieve an average execution time.
The item is having a “type” field that says if it’s the start of the end, an “executionId” to be able to find start and end for a given execution, and we have a “createdAt” field as an ISOString to know when it happened.

I know that i’ll be able somehow to convert to number the ISO string, but what i want to know is if calculate the process duration by doing a diff on 2 data that are on differents items, filter by “executionId” is doable ?
As a sentence it would be : “make a diff between createdAt of start & end elements for all different executionId”

And if it’s possible, how can i do that ?

Thanks a lot, cheer

Hi @hugob first of all, welcome to the QuickSight community :slight_smile:

It will help if you attach a sample dummy data along with expected output, but from what info you have provided, you should be able to achieve your objective using a combination of the following:

  1. Lag - Amazon QuickSight
  2. dateDiff - Amazon QuickSight

Let us know if this helps answer your question. Thanks

2 Likes

Thanks for the quick answer !

For a specific process run, we’re saving those 2 events :

{
  "executionId": "ACTI-834iqhr3-a9e7-4b8e-34raz-sd334Azez",
  "createdAt": "2023-03-23T10:28:13.341Z",
  "source": "MFStart",
  "status": "IN_PROGRESS",
  "triggerType": "WEBHOOK"
}
{
  "executionId": "ACTI-834iqhr3-a9e7-4b8e-34raz-sd334Azez",
  "createdAt": "2023-03-23T10:30:43.071Z",
  "source": "MFEnd",
  "status": "SUCCESS",
  "triggerType": "WEBHOOK"
}

In that case, the process ran for 2.3 sec. I want to be able to calculate that, and do an average for all executions we have.

I’ll look and read what you sent, maybe it works for me.

Thanks again

@hugob And in your QuickSight dataset, these are stored as two rows? If that’s the case, the lag / lead function should definitely help you get one of these “createdAt” into the other row, and then you can cal the difference.

if you’re up for it, you may even do some quick ETL outside of QuickSight to create one row for each executionId with start and end time and avoid the use of lag / lead altogether.

1 Like

Yes they are stored as two rows. I prefer avoid doing the ETL, but if i have too, i will

I tried a bit to work with lag, and i got something that seem to do what i want.
But i have two questions more about display than calculation now.

To be able to calculate the duration, i did

lag(
     createdAt,
     [createdAt ASC],
     1,
     [actionId]
)

And since there are only two items, it works fine, the first createdAt is moved to the second items, and i can do the difference!

But when i display the data as a table, i have to add createdAt and actionId in the fields well, and so both rows of each execution are displayed, even tho there is no real duration in the first one (so it’s useless rows)

So my one/two questions are these :
Is that possible to only display the last value for every unique ID (or hide rows that doesn’t have the duration field calculated) ? And can we display a data (for example this duration) in a display that have only one data in the fields well ? (being able to display the average duration as a single number value, and not as a table with multiple fields)

Hope i am clear enough!

To answer your questions:

  1. Is that possible to only display the last value for every unique ID (or hide rows that doesn’t have the duration field calculated) ? - While there can be multiple ways of achieving this, you could apply a filter to eliminate null values in your calculated duration column

  2. And can we display a data (for example this duration) in a display that have only one data in the fields well ? (being able to display the average duration as a single number value, and not as a table with multiple fields) - You should be able to use KPI visual to display a single metric if I understood your requirement correctly Using KPIs - Amazon QuickSight

1 Like

Ok understood.

I think i got more or less what i wanted, so much appreciated !!
I’ll mark your first answer as solution

Thanks, cheers

1 Like