topic Calculated dimension with count and Set expressions in QlikView

Calculated dimension with count and Set expressions

Mon, 04 Apr 2011 12:24:27 GMT

Hi there,

I've a table like this:

Table:

id,user, type, status

1, luis, 2, D

2, john, 3, D

3, jose, 4, S

4, luis, 4, S

I' need to rank this table with a calculated dimension, that is I need to get the top 2 users whith type = {2, 3} and status = 'D'

I create a calculated dimension like this;:

=IF(Aggr(rank(Count({<type={2,3}, status= {D}>} id)),user)<=2, user)

but it doesn' seem to work. The first part, type={2,3} works all right, but the second part whith the field status doesn't. What am I doing wrong?

Tks,

Luis

Calculated dimension with count and Set expressions

pljsoftware — Mon, 04 Apr 2011 14:24:21 GMT

Hello Luis,

I think that your calculate dimension is correct, it return luis and john that have type 2 and 3 and status D.

Explain why about you the second part whith the field status doesn't work.

Regards
Luca

Calculated dimension with count and Set expressions

Mon, 04 Apr 2011 16:17:18 GMT

Hi Luca,

Table:

Load * inline [

id,user, type, status

1, luis, 2, D

2, john, 3, D

3, jose, 3, S

4, luis, 3, S

5, luis, 3, D

6, luis, 4, D

];

With the table above, my calculated dimension =IF(Aggr(rank(Count({<type={2,3}, status= {'S'}>} id)),user)<=2, user)

and my expression count(id) I was expecting the result:

luis - 1

jose - 1

But I get the result:

luis - 4

jose - 1

That is, I want to count all the rows wich have the type equal to 2 or 3 and status='S' grouped by user.

Tks,

Luis