Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank in Set analysis

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:
SalesmenGlobal turnoverTurnover from the TOP 10 customer
S1sum(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.
2 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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.

.