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

Filter by overlapping groups

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

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

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