Average of calculated aggregated field

Question & Answer
,
1

Hello guys !

My data are in a table where items are looking like
executionId - createdAt - type

The data are all events happening in our processes. So for a single executionId there are multiple items. For example

ex123 - 2023-01-01T10:00:00.000 - workflowLambda
ex123 - 2023-01-01T10:00:10.000 - workflowTransform
ex123 - 2023-01-01T10:00:15.000 - workflowCatch

We are calculating the duration of each execution, using this calculated field

dateDiff(
    parseDate(replace(firstValue(createdat, [createdat ASC], [actionid]), "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"),
    parseDate(replace(lastValue(createdat, [createdat ASC], [actionid]), "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"),
"SS")

So we got in seconds the duration of the execution. But it is displayed on EVERY items of the execution. To hide them we created a calculated field
lead(createdAt,[createdAt ASC],1,[executionId])
That returns null for the last item of each execution, and added a filter to display in the table ONLY items that have null in this calculated field.

So now we have a table where we’re seeing ONLY the last items for each execution, and it display the duration. That’s great.
But now we want a text insight where we’re just displaying the average duration for all executions. Basically doing a avg() or a sum()/count of all duration.
The thing is when i am using those 2 functions, it tells me that i can’t use both non aggregated and aggregated data, and i don’t really succeed to do it.

Can someone help me to obtain a SINGLE value of average duration for all my executions, with what i have already ?

Thanks a lot !!

2

Can you make a kpi with just your dateDiff?

dateDiff(
    parseDate(replace(firstValue(createdat, [createdat ASC], [actionid]), "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"),
    parseDate(replace(lastValue(createdat, [createdat ASC], [actionid]), "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"),
"SS")

I’m using your calculated field above for my dateDiffMax calculation.

image
image968Ă—1192 49.9 KB

Or am I missing something that needs the lead function as well?

Thanks,

1 Like
3

Hey @Max thanks to answer that quick

Yes it looks great! I should have start with that… i was trying so hard to make a complex thing that i didn’t even test with KPI

BUT, it tells me that “Table calculation attribute reference(s) are missing from fields well”. But how do i add those to the field well (createdAt and actionId), i cannot do what you’re doing in this screenshot

4

Oh sorry,

Can you try and make them into a PRE_AGG aggregation? Something like this

dateDiff(maxOver({createdat},[actionid],PRE_AGG),minOver({createdat},[actionid],PRE_AGG))

2 Likes
5

Hey Max,

Thanks for your answer, it works great.
If others would read this, this is what i ended with as a calculated field :

(createdat is a String ISO date)

dateDiff(
    minOver(parseDate(replace(createdat, "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"),[actionid],PRE_AGG),
    maxOver(parseDate(replace(createdat, "Z", ""), "yyyy-MM-dd'T'HH:mm:ss.SSS"),[actionid],PRE_AGG),
"SS")

And i use the KPI with this field as Average

And it makes a great result.
Thanks again Max

1 Like