Hello,
Reading through other's questions and posts in this forum has been a lot of help for me, and I'm hoping this post will be of the same help to others eventually:
Background: I have a table that lists email addresses of people who used an app. Column A contains the list of users, the headers in the remaining columns (B,C,D, etc.) contain the week date, and the value in each value cell is either 1 (yes, user used app this week), 0 (no, user did not use app this week), or -- (user did not have app yet)
Question : Is there a dynamic formula I can add into a new table that tells me, for each week, how many users have used the app at least once each month since the app launch, excluding any weeks where they did not have the app yet? Each month is defined as a set of four weeks, starting from the most recent date (based on the column you are in) and going back as far as needed to capture the app launch date (4/30) (in the example above, if today was 7/2, then month 1 = 6/11-7/2, month 2 = 5/14-6/4, month 3 = 4/16-5/7 ). As I move forward in time, the number of months to capture will continue to grow.
Expected table results:
Reading through other's questions and posts in this forum has been a lot of help for me, and I'm hoping this post will be of the same help to others eventually:
Background: I have a table that lists email addresses of people who used an app. Column A contains the list of users, the headers in the remaining columns (B,C,D, etc.) contain the week date, and the value in each value cell is either 1 (yes, user used app this week), 0 (no, user did not use app this week), or -- (user did not have app yet)
| 4/30 | 5/7 | 5/14 | 5/21 | 5/28 | 6/4 | 6/11 | 6/18 | 6/25 | 7/2 | |
| 1@email.com | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
| 2@email.com | -- | -- | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
Question : Is there a dynamic formula I can add into a new table that tells me, for each week, how many users have used the app at least once each month since the app launch, excluding any weeks where they did not have the app yet? Each month is defined as a set of four weeks, starting from the most recent date (based on the column you are in) and going back as far as needed to capture the app launch date (4/30) (in the example above, if today was 7/2, then month 1 = 6/11-7/2, month 2 = 5/14-6/4, month 3 = 4/16-5/7 ). As I move forward in time, the number of months to capture will continue to grow.
Expected table results:
| As of 4/30 | As of 5/7 | As of 5/14 | As of 5/21 | As of 5/28 | As of 6/4 | As of 7/2 | |||||||||
| Number of users who've used app every monthly | Not enough data
| Not enough data
| Not enough data
| Not enough data
| 2 (the sets of months as of this date are: 1) 5/7-5/28 2) 4/9-4/30 user one is included because he had at least one week of activity in both time spans User 2 is included because he had activity in month 1, and we ignore month 2 because he didn't have the app yet.) | 2 (the sets of months as of this date are: 1) 5/14-6/4 2) 4/16-5/7 user one is included because he had at least one week of activity in both time spans
| 2 | 2 | 2 the sets of months in this date now includes 3 months: 1) 6/4-6/25 2) 5/7-2/28 3) 4/9-4/30 User one is included because he had at least one week of activity in all three time spans User 2 will be included because he had at least one week of activity in months 1 and 2, and we ignore month 3 because he didnt have the app yet | 1 (the sets of months as of this date are: 1) 6/11-7/2 2) 5/14-6/4 3) 4/16-5/7 User one will not count since he has no activity in month 1, so he is not included. User 2 will be included because he has at least one week of activity in months 1 and 2, and we can ignore month 3 because he didnt have the app yet. |
