Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think I found the solution myself 🙂
Avg(Aggr((Max({<
Year = {'2020'}
>} [Incident number])
-
Min({<
Year = {'2020'}
>} [Incident number])
+ 1), Ressource))
I think I found the solution myself 🙂
Avg(Aggr((Max({<
Year = {'2020'}
>} [Incident number])
-
Min({<
Year = {'2020'}
>} [Incident number])
+ 1), Ressource))