Complicated set expression

[#1]

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

[#2]

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

[swuehl · Accepted solution]

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.

[#4]

Thank you very much, it worked

U are my life saver

It turned out  was getting confused with the aggr part.