Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
Try using aggr function...
=Aggr(rank(sum({<Customer={"*"}>}Sales)),Customer)