topic Rank in Set analysis in QlikView

Rank in Set analysis

Wed, 03 Oct 2012 13:25:49 GMT

Hi everybody,

I have been reading this forum for a long time, and now I show up for some help. I am trying to make some TOP 10 calculations within a set analysis but from now I didn't succeed completly.

I would like to calculate for each salesman the turnover they do with their TOP 10 customers.

Someting that should look like that:

Salesmen	Global turnover	Turnover from the TOP 10 customer
S1	sum(Turnover)	sum({$<Customer={"=rank(sum( Turnover))<=20"}>} Turnover
S2	...
S3	...

I already made a part of the job with the expression: sum({$<Customer={"=rank(sum( Turnover))<=20"}>} Turnover)

Problem: the expression works properly only when I select one salesman ; with no selection the Rank function works on all customers and the results are wrong, as if the expression doesn't do the job line per line in my straight table.

I looked at this post: http://community.qlik.com/thread/36393, but could't find any solution either.

Any help will be thankful !

Cédric.

Re: Rank in Set analysis

swuehl — Mon, 15 Oct 2012 12:52:21 GMT

Problem: the expression works properly only when I select one salesman ; with no selection the Rank function works on all customers and the results are wrong, as if the expression doesn't do the job line per line in my straight table.

Exactely, the set expression is evaluated once per chart, not per dimension value. Please check also this doc for a possible workaround:

http://community.qlik.com/docs/DOC-1335

You can create the appropriate expression in the script, as described, the result could look similar to this expression:

=pick(

match(Salesmen,'S1','S2','S3')

,sum({$<Customer={"=rank(sum({<Salesmen={S1}>} Turnover))<=20"}>} Turnover)

,sum({$<Customer={"=rank(sum({<Salesmen={S2}>} Turnover))<=20"}>} Turnover)

,sum({$<Customer={"=rank(sum({<Salesmen={S3}>} Turnover))<=20"}>} Turnover)

)

I would assume that the solution proposed in the referenced thread (

http://community.qlik.com/thread/36393)), using advanced aggregation, will also work out, but it will probably need some adjustments.

If you still have problems getting your issue solved, please post a small sample app with some sample lines of data.

Regards,

Stefan

Re: Rank in Set analysis

Mon, 15 Oct 2012 14:08:14 GMT

Hi Stephen,

Tanks for your answer.

I will have a look to the threads and make some tests.

I will let you know if I am successful.

Regards,

Cédric.