topic Rank by 2 metrics in QlikView

Rank by 2 metrics

slribeiro — Mon, 01 Aug 2016 11:53:00 GMT

Greetings, I have one problem and I was hoping you could help me.

I have the following table:

This table is suposed to be ranked by growth but in the case of having equal values, ranked by Value.

Also I need that the rank values instead of appearing "2-3", appear 2 or 3 separately

So the final table should be something like:

Carlos 1 300 50

Maria 2 2000 30

John 3 700 30

Romi 4 1400 20

I can't make anything like rank(sum(Value*Growth)) because growth is a string and sometimes has values such as 'NEW'

Thanks in advance and best regars.

Re: Rank by 2 metrics

sunny_talwar — Mon, 01 Aug 2016 12:21:25 GMT

Try this:

=Num(Rank(RangeSum(Sum(Growth), Sum(Value)/Max(TOTAL Aggr(Sum(Value), Name))), 4, 1))

Re: Rank by 2 metrics

slribeiro — Mon, 01 Aug 2016 15:49:32 GMT

That would work if I have all integers, but I need to have strings like you can see in the attachment.

But that solution is really really close.

Re: Rank by 2 metrics

slribeiro — Mon, 01 Aug 2016 15:56:56 GMT

So the final order should be alphabetically from Z->A and then decreasing the numbers

Re: Rank by 2 metrics

sunny_talwar — Mon, 01 Aug 2016 15:57:36 GMT

Would you be able to provide information regarding what the expected output needs to look like in this case?

Re: Rank by 2 metrics

slribeiro — Mon, 01 Aug 2016 16:10:01 GMT

Yes of course...

For the example attached, that I think is more clear for you to understand, I should have:

1 Andres 400 NEW

2 Xavier 300 NEW

3 Django 200 NEW

4 Carlos 300 50

5 Maria 2000 30

6 John 700 30

7 Romi 1400 20

The criteria is...

If it's NEW clients stay ordered in the first places by Value, then we order by Growth, then Value in case of being equal.

Re: Rank by 2 metrics

sunny_talwar — Mon, 01 Aug 2016 16:17:28 GMT

Try this:

=Num(Rank(RangeSum(If(sum(Growth) = 0, RangeSum(Max(TOTAL Aggr(Sum(Growth), Name)), 1), Sum(Growth)), Sum(Value)/Max(TOTAL Aggr(Sum(Value), Name))), 4, 1))