Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
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

Tags (2)
1 Solution

Accepted Solutions

Re: Ranking cells by colour in a chart

HI

=Pick(rank( Sum(Value))

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

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

Hope it helps

8 Replies

Re: Ranking cells by colour in a chart

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

Not applicable

Re: Ranking cells by colour in a chart

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

Re: Ranking cells by colour in a chart

HI

=Pick(rank( Sum(Value))

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

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

Hope it helps

Not applicable

Re: Ranking cells by colour in a chart

Brilliant, many thanks for that.

Ralph

Not applicable

Re: Ranking cells by colour in a chart

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

Re: Ranking cells by colour in a chart

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

Not applicable

Re: Ranking cells by colour in a chart

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

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

Thanks

Ralph

Re: Ranking cells by colour in a chart

HI

Yes ..

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

Hope it helps