topic Re: Top 5 Based on Two measures in QlikView

Top 5 Based on Two measures

Wed, 01 Mar 2017 09:08:23 GMT

Hi All,

I am facing issues in finding top 5 suppliers. In table I am calculating % which is first measure, But for some of the suppliers if top % is same then dimension limits picks up random suppliers. For eg. I have selected Top 5 from dimension limit, but if there are 10 Supplier with same % , out of 10, it picks up randomly 5 values in Top 5. Now requirement is, first criteria should be % and second criteria should be number of deliveries, So Out of 10 supplier with same %, Top5 should be highest % with highest number of deliveries.

let's say below data

Supplier	%	Deliveries
AA	100%	66
AB	100%	99
AC	100%	78
AD	100%	97
AF	100%	77
BC	100%	69
KC	100%	98
JK	100%	96
PT	100%	67
KT	100%	51

Note - % and Delivery is calculated

For above data Top 5 should be

OutPut Top 5
Supplier	%
AB	100%
KC	100%
AD	100%
JK	100%
AC	100%

Re: Top 5 Based on Two measures

sunny_talwar — Wed, 01 Mar 2017 09:28:29 GMT

May be use Rank function

If(Rank(Expression% + ExpressionDeliveries/1E10) < 6, Expression%)

and

If(Rank(Expression% + ExpressionDeliveries/1E10) < 6, ExpressionDeliveries)

Re: Top 5 Based on Two measures

Wed, 01 Mar 2017 09:38:47 GMT

Hi sunny,

Sometimes for lowest % , deliveries are high, so above expression is giving wrong results

Re: Top 5 Based on Two measures

sunny_talwar — Wed, 01 Mar 2017 09:40:08 GMT

I guess which one is the priority? % or deliveries? One of them need to take precedence over the other, right?

Re: Top 5 Based on Two measures

Wed, 01 Mar 2017 09:58:14 GMT

first priority is % and then number

Re: Top 5 Based on Two measures

sunny_talwar — Wed, 01 Mar 2017 10:52:38 GMT

Then I don't see any reason why the above won't work .

Would you be able to share a sample where this isn't working

Re: Top 5 Based on Two measures

Tue, 28 Mar 2017 08:56:40 GMT

Hi sunny,

ExpressionDeliveries/1E10.. what is /1E10?

Re: Top 5 Based on Two measures

sunny_talwar — Tue, 28 Mar 2017 09:02:02 GMT

Another way to say 1/10000000000

Re: Top 5 Based on Two measures

Tue, 28 Mar 2017 09:09:17 GMT

How this works? can you give one example?

Re: Top 5 Based on Two measures

sunny_talwar — Tue, 28 Mar 2017 09:39:16 GMT

Its just Scientific notation‌

I guess an example would be 50000000000/1E10 vs. 50000000000/10000000000 should give you 5 in both the cases

Re: Top 5 Based on Two measures

Tue, 28 Mar 2017 10:50:39 GMT

One more help, what could be the expression for bottom 5?

Re: Top 5 Based on Two measures

Tue, 28 Mar 2017 10:52:35 GMT

like this ?

If(Rank( - Expression% + ExpressionDeliveries/1E10) < 6, Expression%)

Re: Top 5 Based on Two measures

sunny_talwar — Tue, 28 Mar 2017 10:55:15 GMT

May be this

f(Rank(-(Expression% + ExpressionDeliveries/1E10)) < 6, Expression%)

Re: Top 5 Based on Two measures

Tue, 28 Mar 2017 11:31:24 GMT

This is not working, but what i suggested is working, not sure why

Re: Top 5 Based on Two measures

sunny_talwar — Tue, 28 Mar 2017 11:37:46 GMT

Whenever in doubt, create a straight table to see what those expression evaluate to

Rank( - Expression% + ExpressionDeliveries/1E10)

Rank(-(Expression% + ExpressionDeliveries/1E10))

and see what the difference is