0 Replies Latest reply: Apr 29, 2010 1:31 PM by Peter Turner RSS

    Aggr function and dimension field

    Peter Turner

      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

      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.