Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Displaying an "x of y values" ranking within table

Hello Everyone,

We are interested in displaying an "x of y" ranking within a table, and I cannot seem to get it to work.

The scenario is this: Our company ("Alpha Company") has market information for us and for the competition.  Within a table, I would like to see Countries on rows and a measure of how we rank vs. the market within the same table.  I don't want to show the other Companies on this table.  Please note that I also don't want to actively select "Alpha Company"

Something akin to this:

Ranking.PNG.png

The ranking is not the ranking of Countries, but the ranking of our Company against the other Companies within that Country.

My presumption is that I'm doing something wrong with the the placement of AGGR().  I thought I had a breakthrough by using ONLY() in conjunction, but it only evaluates once for the whole app instead of going Country by Country.

=ONLY( {<[Company] = {'Alpha Company'}>}AGGR( RANK(SUM(Sales),0,1),[Company])) & ' of ' & COUNT(DISTINCT [Company])

When I use the above, I get this:

Ranking2.PNG.png

It displays the correct number in the text box, and in any country where it just so happens to match (like Austria in the example).

Any help is appreciated.

Best,

Corey

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

or
if you want to use set analysis
=only({<Company={'Alpha Company'}>}aggr(RANK(SUM(Sales)),Country,Company)) & ' of ' & COUNT(DISTINCT [Company])

View solution in original post

3 Replies
Highlighted
Partner
Partner

try this

=only(if(Company='Alpha Company',aggr(RANK(SUM(Sales)),Country,Company))) & ' of ' & COUNT(DISTINCT [Company])

Highlighted
Partner
Partner

or
if you want to use set analysis
=only({<Company={'Alpha Company'}>}aggr(RANK(SUM(Sales)),Country,Company)) & ' of ' & COUNT(DISTINCT [Company])

View solution in original post

Highlighted
Contributor
Contributor

Many thanks!  This did the trick.  I had tried something like this in an earlier version, but on our actual table it's a cycle group.  I had tried GetCurrentField(CycleName) and it didn't like that so I moved on.  I didn't think to try the plain text version of the field name.

In order to get this to work with my cycle group, I had to add a variable and populate the variable with:

vCycleName = '['&GetCurrentField(CycleName)&']'

=ONLY( {<[Company] = {'Alpha Company'}>}AGGR( RANK(SUM(Sales),0,1),$(vCycleName),[Company])) & ' of ' & COUNT(DISTINCT [Company])