top 10 values

[prma7799]

Hi all,

I have one fields called division it has 15 records now am using that as list box.

My requirement is i want only top 10 records from list box.

How can i achieve it.

Thanks

[Kushal_Chawda]

There is no Value field to do the sum..

[prma7799]

Yes this is only dimension value I did sorting on below expression then i will get all top division .

SUM({$<FiscalYear={$(=max(FiscalYear))}>}[Gross Sales Amount])

[Kushal_Chawda]

Try,

=aggr(if( rank(SUM({$<FiscalYear={$(=max(FiscalYear))}>}[Gross Sales Amount]),4)<=10,Division), Division)

[prma7799]

Good Job Friend.

It is working fine.

Can you add me in your Friend list.

Thanks

[datanibbler]

Hello Kush,

I don't quite understand either. I know what aggr() does that IF() statement probably returns a NULL value if a Division is NOT among the top 10, so there's nothing to aggregate and it is not shown.

<=> What I don't understand is, where does that value 4 come from? From the brackets, it seems to be a parameter to rank() - but we are looking for the 10th highest value, not the 4th highest?

Can you please explain where that comes in?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Ah, I see - please just correct me if I got it wrong - the rank() returns - well, the rank of a division rgd. the sum of their sales - the 4 is just the >modus< and tells QlikView what to do in case that two divisions have the exact same sum of Sales - then the first line in the table with this rank would get this rank, the next one would get the next rank and all subsequent ranks would be increased by 1. So this isn't absolutely necessary, but it can be helpful to avoid confusing results, depending on the data.

[Kushal_Chawda]

Hi DataNibbler,

As we are aggregating the Values based on Division there will be a rare case where Value is not available for any of the Division.In this case you are correct ,it will not display anything.

But if value is available for Any one of the Division it will display the Division in listbox only if aggregate function is used. I have used 4 in Rank to display Lowest rank on first row, then incremented by one for each row.