1 Reply Latest reply on Dec 16, 2013 7:16 AM by Simon Peacock

    Can you Filter across Multiple, unrelated Dimensions?

    Simon Peacock


      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)

       

      example.JPG

       

      And using filters I can select a viewpoint on Current, Transition Phase, and strategic phase. Everyone still with me?

       

      good..

       

      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'

      End

       

      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

       

      CASE [Calculation1]

          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'

      END

       

      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

       

      Thanks

       

      Simon

        • 1. Re: Can you Filter across Multiple, unrelated Dimensions?
          Simon Peacock

          Hi

           

          I think I found the answer myself.

           

          It's quite easy actually, suprisingly so (just trying to justify with myself why i needed to ask in the first place!).

           

          Basically I made a parameter, with 3 choices that refer to my viewpoints

           

          like so;

          parm.JPG

           

          then i simply created a calculated field called 'Selection Filter' as follows;

           

          case [Viewpoint to Show]

          WHEN 'Current' then [Current]

          WHEN 'Transition' then [Transition]

          WHEN 'Strategic' then [Strategic]

          end

           

          where [dimensions] are my dimensions or columns listed way above

           

          then i simply pulled the calc filed 'Selection Filter' into my filter pill and selected Y.

           

          And now i have a parm on my viz i can select which viewpoint i need and it works!!

           

          Yay me!

           

          Thanks for looking and I hope if you came here looking for similar answers this helped