topic Calculating a Rank not in a table in QlikView

Calculating a Rank not in a table

Tue, 09 Nov 2010 22:16:22 GMT

Say I have a table of Salespeople and Commission dollars

Salesperson Sum(CommissionDollars) Rank

A 35 1

B 20 2

C 15 3

Next I select Salesperson B. And the table changes to:

Salesperson sum(CommissionDollars) Rank

B 20 1

I want the chart to show the overall rank of salesperson B which is 2. not the rank of just the selected values. I have tried to use Sum({<Salesperson=>} CommissionDollars) but that isn't working

Thanks,

Alex

Calculating a Rank not in a table

johnw — Tue, 09 Nov 2010 22:30:27 GMT

So you're using this for the rank column?

rank(sum({<Salesperson=>} CommissionDollars))

Seems like it should work. What's it giving you? Rank 1? Is it rank 2 but there's some other problem?

Calculating a Rank not in a table

Tue, 09 Nov 2010 23:32:39 GMT

It Gives me then the initial table back with all 3 salespeople. i only want salesperson B.

Thanks for your help

Calculating a Rank not in a table

johnw — Tue, 09 Nov 2010 23:42:25 GMT

Ah, yeah, it'll still calcuate for everyone, so everyone will still be in the table. Well, I'd do this...

if(column(1),rank(sum({<Person=>}Amount)))

...to tell it to null out the second column if there's nothing in the first, which should be 0 based on our selections.

But I'm trying it now, and it's not working. It does show rank 2, but it refuses to remove A and C, even though there are only 0 or null in the columns, and even though I've told it to suppress zeros and suppress missing. I really don't know why it's doing that.

Anyone? What totally obvious thing am I missing to suppress the rows for A and C?

Calculating a Rank not in a table

Tue, 09 Nov 2010 23:51:19 GMT

I actuall think i figured this out after your help. I used an AGGR to pull out only salesperson B.

AGGR( rank(sum({<Salesperson=>} CommissionDollars)), Salesperson)

Calculating a Rank not in a table

johnw — Tue, 09 Nov 2010 23:57:51 GMT

Ah, that works. Still not sure why mine didn't, but at least you figured out how to do what you needed.