Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How do I rank based on dimensions not in a chart

Hi All,

I have a question regarding ranking.

My qlikview app gets a user to to select a customer, there is then a chart Showing the sales for that customer by region. I would then a column that is the rank of that customers sales within that region (taking into account all customers)

My current formula: = Rank(sum([Gross Loss])) will rank the sales for the region against all regions for that one customer (i.e. just taking into account the dimensionality of the chart)

How would I get it to rank the sales of that customer against all other customers within that region (without showing the other customers in the table?)

thanks

Dave

4 Replies
Highlighted
Not applicable

Just guessing a bit here, but set analysis should do the trick for you, try something like:

Rank ( Sum ( { $ < Customer = {"*"} > [Gross Loss] ) )

Hopefully this will help.

Highlighted
Contributor
Contributor

Thanks for your reply Nigel,

it's helped my understanding a bit more but still doesn't give the answer I want yet, but I'm a little closer.

I'll attatch a really basic example so you can see what I mean a bit easier. I've just created some nonsense data to keep the model simple.

For my basic ranking formula: rank(sum(Sales) the customer Steve is ranking 3rd for region Canada - this appears to be calculating the region Canada within Steves sales. Canada ranks 3rd for Sales to Steve.

The formula you gave me: rank(sum({<Customer={*}>}Sales) puts Canada as 2nd. It's ignoring my selection of Customer and telling me Canada is the region with the 2nd highest Sales.

What I want is for the region Canada the rank of Steve by sales against all customers: in my example data he should be 4th.

thanks

Dave

Highlighted
Not applicable

I'm not sure if you ever got an answer, but you could try this based on the example you attached.





rank

(sum({<Region={"*"}>}Sales))



Thanks,

Aline

Highlighted
Creator II
Creator II

Try using aggr function...

=Aggr(rank(sum({<Customer={"*"}>}Sales)),Customer)