topic Re: Find the rank of multiple ranked fields in QlikView

Find the rank of multiple ranked fields

9917mark — Wed, 25 Nov 2020 16:16:04 GMT

Morning to all,

Could I please get some help with the following.

I have 4 unique ranking calculation which give me the respective rank based on

Qty of Tansactions (Rank Trans)

only(aggr(nodistinct rank(count(Partno),4,1), Partno))

Qty Sold (Rank Qty)

only(aggr(nodistinct rank(sum(OrderQty),4,1), Partno))

Sales Value (Rank SalVal)

only(aggr(nodistinct rank(sum(TotalSales),4,1), Partno))

GP Rands (Rank GPR)

only(aggr(nodistinct rank(sum(RProfit),4,1), Partno))

All of which are working perfectly.

What I require is to get an overall ranking based on all of the previous rankings. (Rank Over)

Where currently I can only get the combined sum of the rank.

ie in the Rank Over the results should display :

Your help will be appreciated.

Re: Find the rank of multiple ranked fields

sunny_talwar — Mon, 26 Mar 2018 11:48:36 GMT

Have you already tried this?

Rank(Expression1, Expression2, Expression3, Expression4)

Re: Find the rank of multiple ranked fields

9917mark — Mon, 26 Mar 2018 11:55:48 GMT

I have thanks, as well as adding the four ranks together and then doing an overall ranking on that result.

Re: Find the rank of multiple ranked fields

sunny_talwar — Mon, 26 Mar 2018 11:58:21 GMT

Sorry, that is what I meant

Rank(RangeSum(

only(aggr(nodistinct rank(count(Partno),4,1), Partno)),

only(aggr(nodistinct rank(sum(OrderQty),4,1), Partno)),
only(aggr(nodistinct rank(sum(TotalSales),4,1), Partno)),

only(aggr(nodistinct rank(sum(RProfit),4,1), Partno))))

What do you do when you add this as one of your expression

Re: Find the rank of multiple ranked fields

9917mark — Mon, 26 Mar 2018 13:05:49 GMT

Ok no laughing now,

I modified your suggestion to get the ranking to work. (Rank Over)

Rank(Total(RangeSum(

only(aggr(nodistinct rank(count(Partno),4,1), Partno)),

only(aggr(nodistinct rank(sum(OrderQty),4,1), Partno)),

only(aggr(nodistinct rank(sum(TotalSales),4,1), Partno)),

only(aggr(nodistinct rank(sum(RProfit),4,1), Partno)))),4,1)

And it works perfectly except for one small problem.

It is ranking in the reverse order ????

When I use the example you have posted I get (Sunny)

Re: Find the rank of multiple ranked fields

Sunny Talwar

Thanks for the patience

Re: Find the rank of multiple ranked fields

sunny_talwar — Mon, 26 Mar 2018 13:10:11 GMT

Try this

Rank(Total(-RangeSum(

only(aggr(nodistinct rank(count(Partno),4,1), Partno)),

only(aggr(nodistinct rank(sum(OrderQty),4,1), Partno)),

only(aggr(nodistinct rank(sum(TotalSales),4,1), Partno)),

only(aggr(nodistinct rank(sum(RProfit),4,1), Partno)))),4,1)

Re: Find the rank of multiple ranked fields

sunny_talwar — Mon, 26 Mar 2018 13:11:07 GMT

Ok no laughing now,

Why not? Laughing is a good thing

Re: Find the rank of multiple ranked fields

9917mark — Mon, 26 Mar 2018 13:40:36 GMT

You are a star

Re: Find the rank of multiple ranked fields

stalwar1

Appreciate all the help with this.