Aggr.....................#$£%@!!!

[dinicholls]

Hi All,

I'm not used to using the Aggr function in QlikView, and you lovely people have helped me in the past. I need to adjust this:

=if(Aggr(Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }>}[>60days])),[Group No])<=10,[Group No],Null())

To also include the column 'Name'. I know I can't just add it to my pivot table, and it needs to be added to the above, but where?! Wherever I've put it, the column comes back blank, or all the records are returned, and not just the Top 10.

I've tried reading the Technical Brief on AGGR, but I'm more confused than ever!

Where am I going wrong?!

Thanks

Di

[swuehl · Accepted solution]

The aggr() is like a virtual straight table with 1 or more dimensions and a single expression.

You are currently using GroupNo as dimension and your expression returns the Rank().

Your above comparison outside the advanced aggregation could also be written as

=Aggr(If( Rank(sum({<[Group No] -= { 'GR-000000','GR-A0000' }>}[>60days]))<=10,[Group No]) ,[Group No])

For me, it's easier to understand that this will create a table where the top 10 ranked GroupNo dimensional values will be returned  with their value in the expression, else NULL.

But how do you want to add the  Name here? do you want to concatenate it tot the GroupNo expression result?

If you want to create a new column, you would need to create another calculated dimension.

[dinicholls]

Hi,

I've told them to forget it!!!

They have the information they need in another table, they are just too lazy to look at it!!

Thank you for replying though, my understanding of AGGR has grown!

Thanks

Di