topic How do I rank based on dimensions not in a chart in QlikView

How do I rank based on dimensions not in a chart

Anonymous — Thu, 18 Feb 2010 10:13:19 GMT

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

How do I rank based on dimensions not in a chart

Thu, 18 Feb 2010 14:04:35 GMT

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.

How do I rank based on dimensions not in a chart

Anonymous — Thu, 18 Feb 2010 15:20:10 GMT

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

How do I rank based on dimensions not in a chart

Fri, 07 Jan 2011 18:53:04 GMT

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

How do I rank based on dimensions not in a chart

derekjones — Thu, 17 Feb 2011 15:48:50 GMT

Try using aggr function...

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