Rank in Set analysis

[#1]

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.

[swuehl]

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

[#3]

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.

.