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

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

Wed, 25 Nov 2020 16:16:04 GMT

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.

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

lironbaram — Tue, 28 Feb 2017 11:14:19 GMT

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

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

Wed, 01 Mar 2017 06:19:26 GMT

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.