date range if statement dimension

[#1]

I have the following calculated dimension:

if(Pools<='7/1/2012' and Pools>='8/1/2011','6/30/2013',

if(Pools<='4/1/2012' and Pools>='5/1/2011','3/31/2013',

if(Pools<='1/1/2012' and Pools>='2/1/2011','12/31/2012')))

It creates three rows, one with a date range of 8/1/2011 to 7/1/2012, a second with a date range of 5/1/2011 to 7/1/2011, and a third with a date range of 2/1/2011 to 4/1/2011.

I want to create three overlapping twelve month ranges instead of one twelve and two three month ranges that do not overlap.

Any ideas?

[swuehl]

I don't think you can create a calculated dimension to have one value sorted into multiple categories.

You can create a link table that creates a dimension with the three values needed in your dimension, and then link each dimension values with all dates needed. You can use IntervalMatch() to create this link table or a while loop. For both approaches, there are several samples here in the forum and also on Henrics technical design blog.