Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

9 Replies
Not applicable
Author

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

Not applicable
Author

Thanks for help.

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

Thanks,

Kirti

jagannalla
Partner - Specialist III
Partner - Specialist III

You can achive this through Straigh table.

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

In the txt box give 50.

Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Try this

     Use OrderAmount expression as

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

Celambarasan

Not applicable
Author

Depending on your fields this should work for you.

jagannalla
Partner - Specialist III
Partner - Specialist III

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.

v_iyyappan
Specialist
Specialist

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

Not applicable
Author

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