topic Rank each bar(year) separately in QlikView

Rank each bar(year) separately

dhborchardt — Wed, 09 May 2012 15:06:59 GMT

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.

Re: Rank each bar(year) separately

davematthews — Wed, 09 May 2012 20:50:18 GMT

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.

Rank each bar(year) separately

dhborchardt — Wed, 09 May 2012 21:24:15 GMT

Thanks for the answer and the compliment on my puzzle