Using Rank + Aggr to get the rank number of a specific value out of all possible values

[Paneze]

Hi everyone

I am using this formula to return back a single number, showing what place a specific value of a dimension has out of all the other dimension fields when ranked highest to lowest by a specific measure:

$(=Only({$<[Company Name]={"My Company"}>}Aggr(Rank(Sum({<[Target Type] = {"Profit Margin"}>} [Target met])/Sum({<[Target Type] = {"Profit Margin"}>} [Total])), [Company Name])))  

It worked fine for some target types, but for one in particular it gave me back 134-135, -1 basically. I have no idea why it would do this and work fine for the other target types. 

Any help is much appreciated

Thank you

[JordyWegman · Accepted solution]

Hi Paneze,

First, don't be shy to use some indents, this makes the formula easier to read.

$(=Only( {$< [Company Name] = {"My Company"} >} 
    Aggr(
       Rank(
           Sum({<[Target Type] = {"Profit Margin"}>} [Target met])        
           /
           Sum({<[Target Type] = {"Profit Margin"}>} [Total])
        ), 
    [Company Name])
   )
)  

Can you provide a sample QVF for us? This is easier if we want to solve it.

Jordy

Climber

[Paneze]

HI Jordy, 

I'm not sure how to save as a file, currently using Qlik Sense. The data is also private, so I wouldn't be able to share 😞

[Paneze · Accepted solution]

Hi I added the Num function around rank with mode 4 and that solved the problem. I didn't realise that in some cases of using rank it will give you the difference between two rank numbers.