Count and accumulate members based on month joined (in script)

[#1]

I'm loading a bunch of member data, one field being Date_Joined. I need to do a lot of work based on how many members we had at any particular month in time, accumulated from all months prior to it. Eg.

Month    Members

Jan-15   100

Feb-15   130

Mar-15   180

...

Feb-17   3000

I am able to work out how many members joined in any particular month, but I'm not having luck accumulating from all previous months.

I was hoping I could produce a table in the script, storing each month-year (e.g Jan-2015, Feb-2015...Feb-2017) and say how many members we would have had at that particular point.

I did get this working in a graph, using the accumulate radio button and an above() expression, but I need to use this data in more places than just the graph, and doing it this way really only works if no selections are made.

I've also tried to accomplish this myself by generating the months in a table, which I managed to do, but from there I am not sure how to group and accumulate the member join dates.

I appreciate the help.

[lironbaram · Accepted solution]

hi

in your load script

if you have a table with the fields : MonthYear , members_who_join

than

you can do something like this in the load script

load MonthYear,

        members_who_join

        rangesum(peek('Members',members_who_join) As Members

resident XXXX

order by MonthYear

[#3]

Thanks. I did need to make a temporary table to count the number of members added each month, and then was able to use your solution.