Sum of difference between max and min...?

[blipblopdk]

Hi,

I would like some help with a set expression.

I have attached a picture of the data - also inserted below. Each row in the data set represents an event of absence from work. Some absences are for more days in a row, which means that it is the same "Incident number".

What I would like to do is show number of incidents per person (Resource) in each Area for the Year 2020, and represent this result in a scatter plot, where the "Bubble" is the field "Area", and the second dimension is one that I have not shown in the data set (but I know how to calclutate the one).

Thus for area "Downtown", the result should be: (4 + 3) / 2 = 3,5. And for area "Uptown", the result should be: (3 + 1) / 2 = 2.

Any suggestions as to how I make the right set expression to get the desired result?

I have tried using Max and Min to assess the number of incidents per person in 2020 as:

max({<Year = {'2020'}>}Incident number) - Min({<Year = {'2020'}>}Incident number) + 1.

But then I need to sum this difference across all persons / resources, and I do not know how to achieve that.

BR Kim

Data

[blipblopdk · Accepted solution]

I think I found the solution myself 🙂

Avg(Aggr((Max({<
Year = {'2020'}
>} [Incident number])

-

Min({<
Year = {'2020'}
>} [Incident number])

+ 1), Ressource))