A quick question about whether it’s possible to sort values which are derived from a formula. In the attached example I’m trying to sort the listed risks so that all reds are at the top followed by all ambers, then greens - and so on.
The colour squares are driven by the formula which in turn is driven by the “Impact” and “Likelihood” scores. There is a third digit coming from the folrmula which is derived from the risk’s position on a heatmap. So for example;
Asset Liquidity would be 2.2.2 Change in Competitive Environment would be 3.3.2 Change in Macro Environment would be 5.5.4
(Not vital to the question but for info: The first digit represents the colour of the square on the heatmap (1=green to 5=red) and the following 2 digits represt the impact and likelihood scores as you can see.)
My logic was that using this approach I would be able to sort the risks by colour as well as ‘intra colour’ by just clicking on the sort button. However, this appears to have no effect. Is it because the values aren’t actually in the squares and it’s trying to sort the formula text?
it looks like the color is displayed based on the result from the variable that you have in there. So you should be able to apply the sort on that variable. Do you have any other sorts applied. It may be that you need to move the sort on this variable up in priority.
there should be an option if you go to the properties for the table for the sorts. If I remember right you would click on the detail … button there and can see the sorts applied to the block and change the priorities. My system is actually down so I can’t look for exact location - but I would check there.
The cells we are trying to sort are based on a concatenated string:
Impact & Likelihhod Scores
The Heatmap is 5x5. The location of the risk on the heatmap is determined by a combination of Impact and Likelihood scores (for example; Impact-4, Likelihood-2).
Risk Level
In addition to the numerical values for Impact and Likelihood, we also created a numerical value for the 5 colours in the heatmap:
Dark Green (very low) 1
Light Green (low) 2
Yellow (medium) 3
Amber (high) 4
Red (very high) 5
This is known as the ‘Risk Level’.
Concatenating
Using this numbering system we have concatenated the 3 digits to form one string. The formula looks like this:
As you can see it is combining the Risk Level with the Impact and the Likelihood. The end result is a number string that looks something like this: 3.3.2 (the first digit defines the colour (in this case yellow), the second digit prioritises sorting by Impact and the third digit is Likelihood).
Alerters
We then created an alerter to define the colour based on the first digit. As I understand it, this is equivalent to a simple ‘IF’ statement within conditional formatting.
So
Given that the colour is based on a numerical value (albeit via a formula), the Sort function should work on this table but does not.
QUESTION: Could this be because the report is treating each cell as a seperate table? We have applied the same method to another table with no breaks and it works fine…
(BOB member since 2012-07-30)