Hi, I wonder if anyone out there can help with this.
I have a table that contains application names and some details about their strategic use.
it looks a 'little' like this
App Investment Current Transition Strategic
Bob S Y Y Y
Colin H Y Y N
Dilbert D Y N N
Ethel S N N Y
Fred H N Y N
George D Y N N
So i have dimensions called App as a text, Invesment as colour and want to filter on where current, transtion or Strategic are Y
I've built a really cool looking application landscape which maps out the business function these apps support and what the Investment is (Strategic, Hold, Demise)
And using filters I can select a viewpoint on Current, Transition Phase, and strategic phase. Everyone still with me?
It works well BUT - in order to switch between the different viewpoints i need to filter, and at the moment the only way i can get it to work is by dragging 'Current' into the filter - and then if i want to switch, dragging it out and replacing it with the Transition filter. ideally what I'd like is a filter at Single Value (dropdown) filter to select Current, transition or Strategic.
The problem is, if i use an IF statement, the further down i get i lose values - for example
If [Current] = 'Y' then 'current'
ElseIf [Transition] = 'Y' then 'Transition'
elseif [Strategic] = 'Y' then 'Strategic'
Using this then Colin doesn't show up in my transition filter as he is being classed as Current. If i go to a strategic view i only see Ethel, because poor Bob is classed as current, - so that's no good.
I also tried case, string all together, but that's not working either as you obviously can't have 2 outcomes from a case
WHEN 'YNN' THEN 'Current'
WHEN 'YYN' THEN 'Current'
WHEN 'YYY' THEN 'Current'
WHEN 'YYN' THEN 'Transition'
WHEN 'YYY' THEN 'Transition'
WHEN 'NYN' THEN 'Transition'
WHEN 'YYY' THEN 'Strategic'
WHEN 'NYY' THEN 'Strategic'
WHEN 'NNY' THEN 'Strategic'
WHEN 'NNN' THEN 'Never'
In summary the issue is that something in Transition can also be in Current, and the IF and CASE are definitive.
So if anyone is still with me and has some excellent ideas then I'd be very grateful