Calculate average time with the aggr and set analysis function

[francesctesi]

Hi everyone,

I would like to calculate the average time for exam ID, between two dates.

For this reason, I used the following function:

avg(aggr(DATETIME_FINISH-DATETIME_START, IDEXAM))

However, I would only like to calculate exams, which have the field FLAG_EST = 1 and the field REASON_LATE <> '0'.

Is it possible to apply set analysis to consider exams with these values in the final calculation of the average?

Because if I try to use the filters in different positions, they do not work

[Kushal_Chawda · Accepted solution]

@francesctesi  It is difficult to write set expression without looking at granularity of data and expected output, but try below

avg({<FLAG_EST={1},REASON_LATE-={0}>} aggr(sum({<FLAG_EST={1},REASON_LATE-={0}>} DATETIME_FINISH-DATETIME_START), IDEXAM))

[francesctesi]

thanks so much  @Kushal_Chawda