Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ranking cells by colour in a chart

Hello,

I have a created a straight table with a dimension of 'Site' and a number of different expressions (e.g. Sales, Overheads etc etc as you can see below). What I want to do is assign different colours against each cell based on it's rank, i.e. in the below example for Sales I want to give Glasgow - blue (as it has the most sales), London - green (2nd most sales), Dublin - orange (3rd most sales) and Belfast - red (4th most sales). This would be the same approach for the other expressions and I've also attached the current expression I have (the 'Week' field is based on a variable to look back at the last 6 weeks). Any help as to how I can implement this would be most appreciated.

BelfastLondonDublinGlasgow
Sales23433267
Overheads123245563321
Attendance3.34.11.14.5
Output5467900213

Sum

({<Site={"Belfast"}, Week={$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Sales)

Thanks,

Ralph

1 Solution

Accepted Solutions
MayilVahanan

HI

=Pick(rank( Sum(Value))

,Blue(),Green(), black(),red())

In this instead of sum(value) use your expression..

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

8 Replies
MayilVahanan

Hi

Try like this

Click the '+' sign in the expression n select the background color , and apply like this

=Pick(rank(

Sum

({<Site={"Belfast"}, Week={$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Sales)

,Blue(),Green(), black(),red())

edit:

=Pick(rank(

Sum

({<Site={"Belfast"}, Week={$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Sales))

,Blue(),Green(), black(),red())

//missed ')' this

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Unfortunately this didn't work. It shows up as an error in the expresion for the comma after Green()

MayilVahanan

HI

=Pick(rank( Sum(Value))

,Blue(),Green(), black(),red())

In this instead of sum(value) use your expression..

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Brilliant, many thanks for that.

Ralph

Not applicable
Author

I've just noticed that while this works in 99% of scenarios it doesn't when 2 sites have the same data and aren't on the highest or lowest end of the scale. e.g.

Site A Sales - 43

Site B Sales - 43

Site C Sales - 53

Site D Sales - 12

In this situation Site D would have red against it, Site C would have green against it but Site A and Site B's background would be blank. I suppose the issue is what colour to attribute to the Sites in the middle, orange (below red) or yellow (above green)?

Any ideas

Thanks,


Ralph

MayilVahanan

HI

  Try like this

  Rank(Sum(Value),0,1)

It takes both 43,43 as same rank , in this case its 2. and

     53 as 1

     12 as 4

so, for value 43 , it appear as green color,

12 as red

53 as blue

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Do I still use pick in the above expression, i.e.

Pick(Rank(Sum(Value),0,1),Blue(),Green(), black(),red())

Thanks

Ralph

MayilVahanan

HI

Yes ..

Pick(Rank(Sum(Value),0,1),Blue(),Green(), black(),red())

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.