How to calculate inforce members

[#1]

I have attached an Excel file which shows what I need to do.

The data I have contains list of members and for each member it has a start date and an end date. I want to know at any point in time how many members are inforce or active and how has this evolved over time. The data is shown in the first sheet and on the second sheet it first calculates the active members at mid of each month and then the graph just displays this.

My aim is to get either the table or the graph.

Thanks.

[Gysbert_Wassenaar]

See this blog post: Creating Reference Dates for Intervals

And the attached example. If you don't really mean 'at any point in time', but are willing to make do with the first days of each month then you can change

Table2:

Load id, Date(START_DT + iterno() -1) as ReferenceDate

resident Table1

While IterNo() <= END_DT - START_DT +1;

to

Table2:

Load id, Date(addmonths(monthstart(START_DT), iterno() -1)) as ReferenceDate

resident Table1

While Date(addmonths(monthstart(START_DT), iterno() -1) )<= END_DT ;

[tresesco · Accepted solution]

PFA, Hope this is what you need.

[#4]

Thanks. Finally got the professional license and was able to view your file and it is quite helpful.

The second part of the code is not working for me the Makedate part, however, I just loaded the date table inline manually and it serves the purpose.