topic Re: Rank Function in QlikView

Rank Function

Anonymous — Tue, 08 Sep 2015 21:25:17 GMT

I have a issue that I have not been able to find a work around for. I am trying to populate a text box with the rank of a sale person relative to the total sales person population. To do this I have been using AGGR with Rank in the formula below. This works fine until I filter by a product where the sales person has not sold anything. In those cases it is returning "-" in the text box. Ideally it would treat no sales as $0 sales and return the appropriate rank.

Any insight would be greatly appreciated.

=Aggr(Rank(Sum({_MTDFlag={1},SalesPerson=>}Sales),1,1),SalesPerson)

Selections

Salesperson List Box

- Salesperson A

Sum Result

Sum({_MTDFlag={1},SalesPerson=>}Sales)

-$0

Expected Result

Current Result

Re: Rank Function

qlikviewwizard — Wed, 09 Sep 2015 03:29:01 GMT

Hi,

Can you attach sample qvw with the data. Thank you.

Re: Rank Function

sunny_talwar — Wed, 09 Sep 2015 04:32:50 GMT

Does anything changes when you use this:

=Avg(Aggr(Rank(Sum({_MTDFlag={1},SalesPerson=>}Sales),1,1),SalesPerson))

Re: Rank Function

Anonymous — Wed, 09 Sep 2015 13:08:38 GMT

I have attached a simplified version of the data.

Thanks

Re: Rank Function

Anonymous — Wed, 09 Sep 2015 13:09:04 GMT

Unfortunately that did not work. Thanks for the suggestion.