Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create and overall ranking for a publication and then a ranking by year. The overall ranking is working fine. Here is my calculated dimension: Overall Rank =Aggr(Num(Rank(Sum(Aggr(count(DISTINCT DocID),PubName)),4,1),'##'),PubName) .
I would like a Ranking per year.
Year | PubName | Overall Rank | Year Rank | # of Pubs (DocId) |
---|---|---|---|---|
2000 | A | 1 | 1 | 25 |
2000 | B | 2 | 2 | 15 |
2000 | C | 3 | 3 | 10 |
2001 | A | 1 | 1 | 10 |
2001 | B | 2 | 3 | 2 |
2001 | C | 3 | 2 | 5 |
2002 | B | 2 | 2 | 9 |
2002 | C | 3 | 1 | 10 |
What is Overall Rank vs Year Rank?
Create a Straight Table
Dimension
Year
PubName
Expression
Sum(DocId)
Rank(Sum(DocId),4)
Overall Rank is the Ranking for the total number of Distinct Doc ID. Year Rank is the Ranking for the number of Distinct Doc id for a given year.
Please check the solution provided.. Is this what you are looking for? If not... kindly provide your expected result..
Hi Frank,
i hope the table below will help you
Year | PubName | Sum ([# of Pubs (DocId)]) | =Rank(TOTAL [# of Pubs (DocId)],4,1) | =Rank( [# of Pubs (DocId)],4,1) |
---|---|---|---|---|
2000 | A | 25 | 1 | 1 |
2000 | B | 15 | 2 | 2 |
2000 | C | 10 | 3 | 3 |
2001 | A | 10 | 3 | 1 |
2001 | B | 2 | 8 | 3 |
2001 | C | 5 | 7 | 2 |
2002 | B | 9 | 6 | 2 |
2002 | C | 10 | 3 | 1 |
I described a number of interesting issues related to ranking in my blog post - please read it here:
Q-Tip #8 – Ranking the Unrankable | Natural Synergies
cheers,
Oleg Troyansky
www.masterssummit.com - take your QlikView skills to the next level!