topic Re: Rank ? in QlikView

Rank ?

Wed, 09 Sep 2015 17:12:51 GMT

Hi All,

How to Calculate in Rank in Back end Scripting for the Getting the TOP 10 Customers...

Kindly help me on this...

Regards,

Helen

Re: Rank ?

sunny_talwar — Wed, 09 Sep 2015 17:14:57 GMT

Would you be able to share a sample where it can be demonstrated?

Re: Rank ?

sinanozdemir — Wed, 09 Sep 2015 17:40:33 GMT

As sunindia indicated it would be better if you could share a sample. Although, you want to see a working example, here is one for you:

In the back-end, I usually use Peek() function to rank the records:

This will give you the below data model:

And in the front-end, you can use nest Rank() with Aggr():

You use 1 in the set analysis so that ranking doesn't change based on users' selections.

I am attaching the qvw, hope this helps.

Re: Rank ?

sunny_talwar — Wed, 09 Sep 2015 17:42:57 GMT

Just to add to what Sinan‌ mentioned, I have also used AutoNumber() function to create Rank, but it all depends on what is the exact requirement

Re: Rank ?

Anonymous — Wed, 09 Sep 2015 17:44:22 GMT

use order by in script to arrange your field value in a particular order (ASC or DESC) and then use rowno() function to identify the rank. Let me know if it helps.

Re: Rank ?

Thu, 10 Sep 2015 09:43:55 GMT

HI Sinan,

Thanks..

But I need to bring Top Customers, on the basis of Last 12 Months in back end, I need to show them in front ENd ..with Salesvalue

Regards,

Helen

Re: Rank ?

Kushal_Chawda — Thu, 10 Sep 2015 09:57:51 GMT

You mean to say, customers having sales in last 12 months?

Re: Rank ?

Thu, 10 Sep 2015 10:01:13 GMT

Customers who are having top Sales in last 12 Months, I need to bring 10 Customers as Out put..

Last 12 MOnths: Sep-2014 to Aug 2015 having top Sales

Regards,

Helen

Re: Rank ?

Kushal_Chawda — Thu, 10 Sep 2015 10:08:13 GMT

can you share some sample data?

Re: Rank ?

Thu, 10 Sep 2015 10:18:08 GMT

U can use this..

Re: Rank ?

sinanozdemir — Thu, 10 Sep 2015 12:42:57 GMT

In the spreadsheet, the maximum order date is 12/29/2009.

Re: Rank ?

Thu, 10 Sep 2015 14:14:56 GMT

Sample data i have shared, u can Fix the Last 12 Months Data & Bring the Top 10 Customers

Regards,

Helen

Re: Rank ?

sinanozdemir — Thu, 10 Sep 2015 14:59:07 GMT

Ok. I don't think you need to handle this in the back-end at all.

I did everything in the front-end:

Total Sales within last 12 Months:

And Ranking in another column:

This is how I would do it, maybe someone else has a different approach. You can also combine the ranking part within a set analysis to only return top 10 customers.

Hope this helps

Re: Rank ?

Kushal_Chawda — Fri, 11 Sep 2015 11:50:30 GMT

HI hbetty2012

Is there any specific reason to do it from back end?

Re: Rank ?

Thu, 01 Oct 2015 05:35:03 GMT

Yes..

Because I need to bring it from Top 15 Stockists based on Last 12 Months Sales..

For Example: Current Month is --Oct-2015

Top 15 Stockists --Oct-2014 to Sep-2015

Bifurcation i need to Show as MTD Sales..If any of the 15 Stockists not having the Sales Value in all the MTD Months wise bifurcation that particular stockist is Missing because that particular stockist not having Sales, but Still i need to Show that particular Stockist with Sales Value as 0 in the MTD wise bifurcation..

Regards,

Helen