2 Replies Latest reply on Aug 17, 2012 6:48 AM by giles.somers

    Filter by overlapping groups

    giles.somers

      I've tried several approaches, but doing this seems surprisingly complicated.

       

      My aim is to have a parameter control that picks certain groups within a dimension

      This is fairly easy for separate groups (ie. East, West, North, South regions of US states dimension), but not for overlapping.

      In other words, how does one set up a control that offers different group selections?

      ie. States starting with letter 'A', States with kyotes, States that banned execution.

       

      What I want is something more simple.

      I have a dimension with years: 2001 through to 2021

       

      What I'd like to be able to do is pick say

      - 2001-2010

      - 2011-2021

      OR

      - 2004-2010

      - 2008-2015

       

      I have attempted several table calculations, but cannot find a way to specify certain years

      ie.something like

      CASE IF ' 2001-2010' THEN YEAR {pick years 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010}

       

      Alternatively, is it even possible to say in a calculated field for use in a filter, something like 'pick years less than 2010'??

       

      Thanks for any help

        • 1. Re: Filter by overlapping groups
          Peter Hopwood

          Giles,

           

          To do the year grouping problem you have described,  how about creating a parameter ([Year Groups]) , then building a calculated field setting up the case statement like this:

           

          Case [Year Groups]

          when '2009-2010' then IIF(year([Order Date]) = 2009 or year([Order Date]) =2010, 1, 0)

          when '2009-2011' then IIF(year([Order Date]) = 2009 or year([Order Date]) =2010 or year([Order Date]) =2011 , 1, 0)

          when '2009-2012' then IIF(year([Order Date]) = 2009 or year([Order Date]) =2010 or year([Order Date]) =2011 or  year([Order Date]) =2012 , 1, 0)

          end

           

          You can then filter on the 1 or 0 returned.

           

          This could get to be a pain for lots of combinations but I've tested it in the attached workbook and it seems to work ok. You could of course modify the if statement to use <2010 or >2010 or another set of rules.

           

          Hope this helps

           

          Peter

          • 2. Re: Filter by overlapping groups
            giles.somers

            Hi Peter

            Thank you very much, that worked perfectly.

            I find it hard to know how exactly to write the calculated fields - just one mistake can cause an error.

            I'll be passing your explanation on to colleagues, as it's a useful one to know.

            Cheers

            Giles