Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Belfast | London | Dublin | Glasgow | |
---|---|---|---|---|
Sales | 23 | 43 | 32 | 67 |
Overheads | 123 | 245 | 563 | 321 |
Attendance | 3.3 | 4.1 | 1.1 | 4.5 |
Output | 54 | 67 | 900 | 213 |
Sum
({<Site={"Belfast"}, Week={$(=vCurrentWeek),$(=(vCurrentWeek-2)),$(=(vCurrentWeek-3)),$(=(vCurrentWeek-4)),$(=(vCurrentWeek-5)),$(=(vCurrentWeek-1))}>} Sales)
Thanks,
Ralph
HI
=Pick(rank( Sum(Value))
,Blue(),Green(), black(),red())
In this instead of sum(value) use your expression..
Hope it helps
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
Unfortunately this didn't work. It shows up as an error in the expresion for the comma after Green()
HI
=Pick(rank( Sum(Value))
,Blue(),Green(), black(),red())
In this instead of sum(value) use your expression..
Hope it helps
Brilliant, many thanks for that.
Ralph
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
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
Do I still use pick in the above expression, i.e.
Pick(Rank(Sum(Value),0,1),Blue(),Green(), black(),red())
Thanks
Ralph
HI
Yes ..
Pick(Rank(Sum(Value),0,1),Blue(),Green(), black(),red())
Hope it helps