9 Replies Latest reply: Apr 26, 2012 11:19 AM by kirtikakkar

# Top 50 names as dimesion of pivot table

Hi All,

I need to have top 50 of one field say names in rows of a pivot table. In other words, a dimension of top 50 names must be calculated in the script.

Please suggest most efficient way to do this.

Also, do we have rank function in Qlikview?

Thanks,

Kirti

• ###### Top 50 names as dimesion of pivot table

aggr(if(rank(sum({1}[Order Amount]))<=50,Customer),Customer)

Example above will return for our data the top 50 customers no matter what is selected. Remove the {1} to change this based on selections

• ###### Top 50 names as dimesion of pivot table

Thanks for help.

Please confirm if this will give me 50 customer names having highest order amount among all.

Thanks,

Kirti

• ###### Top 50 names as dimesion of pivot table

You can achive this through Straigh table.

ST -> Properties -> Presentation Tab -> Max Number (1-100)

In the txt box give 50.

• ###### Top 50 names as dimesion of pivot table

I need 50 customer names having highest order amount among all. Not first 50 records.

• ###### Top 50 names as dimesion of pivot table

Keep Max Number as 50 and change the sort order.

In Sort Tab -> Expression as Sum(OrderAmount) and keep it as descending order for expression.

Don't forget to uncheck all other sorting orders.

Hope it helps you.

• ###### Top 50 names as dimesion of pivot table

Depending on your fields this should work for you.

• ###### Top 50 names as dimesion of pivot table

Hi,

Try this

Use OrderAmount expression as

Sum({<Customer={"=Rank(Sum([Order Amount]))<=50"}>} [Order Amount])

Celambarasan

• ###### Top 50 names as dimesion of pivot table

hi,

Use the Chart properties below like

ST -> Properties -> Presentation Tab -> Max Number (1-100)

In the txt box give 50.

And check the sorting order

dimension  = 'descending order'

then you will get the top 50 customer.

Hope its help

Regards,

Iyyappan

• ###### Top 50 names as dimesion of pivot table

Thank you so much guys... This is working now...

There is another challenge now. Above these top 50 customer names , I need another field in a row which is a count.

What have I done till now is, I have added customer name in to dimension tab and sorted it on the basis of order amount in sort tab. Expression tab has formulas on order amount. Now I need  another diemsion(row above customer names) which is a count of customers. We have handled such cases before using value list but in this we need to have top 50 customer names which are not static.

Please confirm if I am able to explain problem properly. Will try to elaborate otherwise.

Thanks,

Kirti