Count number of 'active' dates

[#1]

Hi,

I'm looking to create a bar chart with months as the X axis, and the number of 'open' dates between two fields. My data set looks like this (but with over five thousand records, spanning over ten years):

Open          Closed

1/1/2000     1/6/2000

1/3/2000     1/9/2000

1/2/2002     1/7/2006

The day is always the 1st of the month, so I just need a count of the months. I've looked over this forum, and the LoadInline function doesn't look like it will work here, as it will generate too many records.

Thanks very much for your help!

[jpapador]

I'm confused do you want a count of open days by the month of the open date?  You say at the end you just need to count the months, not sure what you are trying to do.

[#3]

The bar chart will display Jan, Feb, Mar... and for each, the number of currently 'open' (a count of the number of dates that are open, and not closed) dates.

[jpapador]

I think this is what you are looking for.  If you make your dimension Month (based off of open date) and your expression Sum(Num(ClosedDate) - Num(OpenDate)) it will add up the number of days between those 2 dates based on your dimension

[#5]

hey Oliver,

Pls check out the attached file.

Hope this helps

Thanks

AJ

[Gysbert_Wassenaar]

See this blog post: Creating Reference Dates for Intervals