Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
See attached qvw.
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.
See attached.
Cheers.
Hi Christopher. Thanks. Something wrong with your formula. It doesn't count users the way I need.
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.
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 |
try
aggr(sum(activity),date)