Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count number of 'active' dates

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!

5 Replies
jpapador
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

hey Oliver,

Pls check out the attached file.

Hope this helps

Thanks

AJ

Gysbert_Wassenaar

See this blog post: Creating Reference Dates for Intervals


talk is cheap, supply exceeds demand