how to count memeber for two dates in one table - help needed

[wizardo]

hi guys

i have one table

members

each member have joining date and posibly leaving date

what i need to do is show a table with each year the count of leaving and the count of joining per this year

so if this is my table:

member join_date left_date

A 2006 2007

B 2008 2009

C 2005 2007

D 2007 2008

i need to get this table returned

Year # joined # left

2005 1 0

2006 1 1

2007 1 2

2008 1 1

2009 0 1

any ideas?

wizardo

[disqr_rm · Accepted solution]

See enclosed. I know this can be done wtih Set Analysis as well, but I do not have v9 right now with me.

Anyway, you would get an idea. Hope this helps.

Rakesh

[wizardo]

Hi Rakesh,

i was so concentrated on solving this by using a calculated dimension (with the valuelist function) that i just didnt see the obvoiuos.

thanks for lighting my way to the truth:)

just what i needed

wizardo