topic Complicated set expression in QlikView

Complicated set expression

Tue, 17 Jan 2012 15:07:19 GMT

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

Complicated set expression

Tue, 17 Jan 2012 15:09:51 GMT

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 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

Re: Complicated set expression

swuehl — Tue, 17 Jan 2012 15:35:53 GMT

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.

Complicated set expression

Tue, 17 Jan 2012 15:44:52 GMT

Thank you very much, it worked

U are my life saver

It turned out was getting confused with the aggr part.