Rank each bar(year) separately

[dhborchardt]

I have figured out how to rank the data and sort it from largest amount on the bottom to the least at the top. This works fine for the first bar and then each subsquent bar as long as the cities stay in proportion. If a city that was ranked 3 now has a value that would rank it 2 it will not move to the 2nd position. I would like each year to be re-ranked so the largest is alway at the bottom and so forth. I have included a sample of what I have so far.

[davematthews · Accepted solution]

Hi Dhborchardt,

I hope I understand you right;

You can map the places to colors in the script:

Load
 * INLINE [
Place, ColorCode
minneapolis, 1
Los Angeles, 2
Tampa, 3
Austin, 4
Seattle, 5
Miami, 6
Chicago, 7
];

LOAD * INLINE [
Year, Amount, Place
  2010, 6, minneapolis
    2010, 7, Los Angeles
    2010, 4, Tampa 
....

then you can bring them in using the background color option in the expression tab "sum(Amount)" expression.

Use =color(ColorCode)

Then create a dim as follows:

=dual(Place,Aggr(Rank(Sum(Amount),4,1),Year,Place))

You can use the dimension limits in qv11 to restrict the displayed values. However you may find that not all values appear in the legend.  Perhaps create your own legend using a single column straight table. Or let the users just filter.

[dhborchardt]

Thanks for the answer and the compliment on my puzzle