topic Re: 3 month running(sliding) count in QlikView

3 month running(sliding) count

Thu, 19 Sep 2013 17:16:06 GMT

Hello guys,

Need your help.

Have a list of users (a,b,c,d) that each month might have or have not some activity. I need each month to count how many users had activity during last 3 months (including current month).

!!!This table is just to demonstrate how the users should be counted. Actual data structure is a bit different and is in the attachment!!!

month	a	b	c	d	# of users with activity
Jan-13	1	1		1	3
Feb-13	1	1		1	3
Mar-13		1		1	3
Apr-13				1	3
May-13				1	2
Jun-13				1	1
Jul-13				1	1
Aug-13				1	1
Sep-13			1	1	2
Oct-13			1	1	2
Nov-13			1	1	2
Dec-13	1		1	1	3

Thanks a lot in advance.

Re: 3 month running(sliding) count

Gysbert_Wassenaar — Thu, 19 Sep 2013 17:27:23 GMT

See attached qvw.

Re: 3 month running(sliding) count

Thu, 19 Sep 2013 20:04:10 GMT

Thanks man. Unfortunately it's not an option for me to use load scripts I simply do not have rights for that. I can only use chart expressions.

Re: 3 month running(sliding) count

Fri, 20 Sep 2013 18:22:10 GMT

See attached.

Cheers.

Re: 3 month running(sliding) count

Mon, 23 Sep 2013 07:52:07 GMT

Hi Christopher. Thanks. Something wrong with your formula. It doesn't count users the way I need.

Re: 3 month running(sliding) count

Mon, 23 Sep 2013 13:07:43 GMT

Ok. What should the numbers be? You said count the number of users for the past 3 months. So for month 01.01.2013 its 3 + 3 + 2 = 8. 01.02.2013 = 3 + 2 + 1 = 6.

Re: 3 month running(sliding) count

Mon, 23 Sep 2013 13:29:36 GMT

Hi:) The right counting is shown in this table. Ones ('1') are meant to show whether user had or not activity in the respective month. For instance in January 2013 users a, b,d had activity while c did not. "# of users with activity" here shows correct counting (in Jan there were 3 users that had activity in last 3 months). Hope it's clearer now.

month	a	b	c	d	# of users with activity
Jan-13	1	1		1	3
Feb-13	1	1		1	3
Mar-13		1		1	3
Apr-13				1	3
May-13				1	2
Jun-13				1	1
Jul-13				1	1
Aug-13				1	1
Sep-13			1	1	2
Oct-13			1	1	2
Nov-13			1	1	2
Dec-13	1		1	1	3

Re: 3 month running(sliding) count

Mon, 23 Sep 2013 19:08:05 GMT

try

aggr(sum(activity),date)