topic Re: Doubt about rank() in QlikView

Doubt about rank()

Wed, 23 May 2012 16:11:12 GMT

Hello,

Please, can someone help me about a rank() graph?

I have a table with fields: Country, Seller, Amount, Discount.

How I do a graph showing for each Country, Sum(Amount) of top ten Sellers (ranking about Amount sold on that Country) and Sum(Discount) of top ten Sellers (ranking about Amount sold on that Country)?

I think it works by using rank() and aggr() commands, but I can't.

Thank you very much.

Re: Doubt about rank()

Jason_Michaelides — Wed, 23 May 2012 16:31:07 GMT

Dimension = Country

Expression = Sum({<Seller={"=Rank(Sum(Amount))<=10"} >} Amount)

(I think - bit rushed...)

See attached,

Jason

Re: Doubt about rank()

Fri, 25 May 2012 07:02:20 GMT

Hi Jason, thank your answer.

I think I didn't explain it very well. What I mean, I need to show all countries, and for one country y need to show total amount of top ten sellers, but ranking about sells only on than country.

I tried with somethink like:
Dimension: Country
Expresion: Sum( {$<Seller= {"=rank(aggr(sum(Amount),Seller)) <= 10"}>} Amount)

But it shows amount only for whole top ten sellers, Instead of amount of top ten sellers of each Country.

Thank you very much.

Re: Doubt about rank()

Jason_Michaelides — Fri, 25 May 2012 07:22:41 GMT

Hmm...I'll have another look when I get to my desk. Can you post a sample of your app to make it easier please.

Re: Doubt about rank()

Fri, 25 May 2012 08:19:18 GMT

Sorry, Jason, I can't. Company policy.

But I modified your qv with one exemple.

Re: Doubt about rank()

Fri, 25 May 2012 09:51:21 GMT

I can do this if I include the Seller dimension, but I can't totalize, and when I colapse Country dimension it doesn't work.

Please, does someone know to do this?

Re: Doubt about rank()

Jason_Michaelides — Fri, 25 May 2012 10:16:35 GMT

I'm at the same place. Got it working in a pivot with seller dimension expanded but not collapsed. If only 1 country is selected it works OK. This is because Set Analysis only calcluates once per chart so the list of sellers to include is global.

Tricky one - there will be a way!

Is the ranking dependent upon user selections? If not you could identify the top 10 sellers per country in the script and flag them.

Re: Doubt about rank()

Fri, 25 May 2012 10:19:30 GMT

Hi Jason, thank you.

Yes, the ranking must be dinamic...

Re: Doubt about rank()

Jason_Michaelides — Fri, 25 May 2012 12:18:56 GMT

I went for a swim and had a Eureka! moment...we need to put the Aggr() and Rank() into a dynamic dimension for Country, not seller. Simple straight table:

Dimension: =Aggr(if(Rank( Sum(Amount))<=10, Country),Country,Seller) - you will need to tick "Suppress when Null"

Expression: Sum(Amount)

See attached.

There's always a way...

Jason

Re: Doubt about rank()

Fri, 25 May 2012 12:39:15 GMT

Thank you very mutch!!

I tried with dimension too, editing Country dimension and I could not. But it is perfect.

Thank you one more time.

Re: Doubt about rank()

Jason_Michaelides — Fri, 25 May 2012 13:07:19 GMT

Pleasure - good challenge, that one