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.
![error loading image](/legacyfs/online/-1526_sourceID:1526)
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.