Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am totally confused with a set expression that i want to write.
I have 4 dimentions: name, department, relation and score. For each department, i want to have the average score of each person (that has the same relation), and then i want to take the median of those values, and put it in a graph that has the relation dimension. (So i need 3 median values for each relation.)
As an example, lets say this is my data:
In the end, i need 2 diferent charts for each department.
For department a,
relation 1 needs to have the bar with this value : median (avg(2,3,3,4),avg(6,3,2),avg(3,4,5)) = 3,6
relation 2 needs to have the bar with this value: median (avg(3),avg(4,5)) = 3,75
relation 3 needs to havethe bar with this value: median(avg(4)) = 4
For department b,
relation 1 needs to have the bar with this value :median (avg(4),avg(7),avg(1,2)) = 4
relation 2 needs to have the bar with this value :median (avg(5,6)) =5,5
relation 3 needs to have the bar with this value :median(avg(2,3)) = 2,5.
I highly appriciate any help.
Thank you very much,
Duygu
I think there is no complicated set expression needed, please see attached.
Just using a Trellis chart with advanced aggregation:
=median (aggr(avg(score),department, relation, name))
You can get rid of the Trellis and create two separate charts, of course.
Regards,
Stefan
edit:
Attached a sample with separate charts.
sorry, there is no example data in the post, it didnt appear, so here it is :
name department relation score
Alice a 1 2
Alice a 2 3
Alice a 3 4
Alice a 1 3
Alice a 1 3
Alice a 1 4
Alice b 1 4
Bob b 2 5
Bob b 2 6
Bob b 1 7
Dlyan a 1 6
Dlyan a 2 5
Dlyan a 2 4
Dlyan a 1 3
Dlyan a 1 2
Ruth b 1 1
Ruth b 1 2
Ruth b 3 2
Ruth b 3 3
June a 1 3
June a 1 4
June a 1 5
I think there is no complicated set expression needed, please see attached.
Just using a Trellis chart with advanced aggregation:
=median (aggr(avg(score),department, relation, name))
You can get rid of the Trellis and create two separate charts, of course.
Regards,
Stefan
edit:
Attached a sample with separate charts.
Thank you very much, it worked
U are my life saver
It turned out was getting confused with the aggr part.