
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using Rank + Aggr to get the rank number of a specific value out of all possible values
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 😞

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
