Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks for help.
Please confirm if this will give me 50 customer names having highest order amount among all.
Thanks,
Kirti
You can achive this through Straigh table.
ST -> Properties -> Presentation Tab -> Max Number (1-100)
In the txt box give 50.
I need 50 customer names having highest order amount among all. Not first 50 records.
Hi,
Try this
Use OrderAmount expression as
Sum({<Customer={"=Rank(Sum([Order Amount]))<=50"}>} [Order Amount])
Celambarasan
Depending on your fields this should work for you.
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.
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
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