Aggr function and dimension field

peter_turner — Thu, 29 Apr 2010 17:31:04 GMT

Hello,

I'm trying to use the following expression as the first dimension on my pivot table, hiding null values.

The idea is that i can display only the:
Top 5 rows Per 'Field 1' (top level on the pivot table)
Based on the rank generated by a count of field MY_Field.

=if(aggr(rank(Count(MY_Field)), Field1)<=5,Field1,null())

It should result in a table with 5 rows per each value of 'Field1', and have the top 5 ranking values based on the count()
The problem is that my chart displays many rows per 'Field1', when i was hoping for only the top 5.
I suspect its todo with the aggr() function and the dimensions, but i'm not sure.

I've had to blur the data, but what i should see is only 5 rows for the value 'Group1' ,ignoring my other values from Field2,Field3, Field4,
then another 5 rows for 'Group2' etc etc.

I'm open to any better ideas or changes 🙂

Thanks,
Peter.

topic Aggr function and dimension field in QlikView

Aggr function and dimension field