Custom Week number from Acquistion Date of each User

Question

Is there a way to calculate Custom Week numbers that start from a user's first Transaction Date onwards? The Users (emailId) and TransDate columns may not be in a sorted condition as shown below:

e.g.

+------+-------------+---------------------+
| WkNo |  TransDate  |       emailId       |
+------+-------------+---------------------+
|    1 | 2018-Aug-30 | moz.shea@abc.com    |
|    1 | 2018-Aug-30 | moz.shea@abc.com    |
|   10 | 2018-Nov-07 | moz.shea@abc.com    |
|    1 | 2018-Aug-09 | zabi.prado@abc.com  |
|    1 | 2018-Aug-09 | zabi.prado@abc.com  |
|    6 | 2018-Sep-20 | zabi.prado@abc.com  |
|   15 | 2018-Nov-23 | zabi.prado@abc.com  |
|   21 | 2018-Dec-31 | zabi.prado@abc.com  |
|    1 | 2018-Aug-20 | silo.whitte@abc.com |
|    5 | 2018-Sep-23 | silo.whitte@abc.com |
|    7 | 2018-10-11  | silo.whitte@abc.com |
|    7 | 2018-10-11  | silo.whitte@abc.com |
|    8 | 2018-Oct-14 | silo.whitte@abc.com |
|    9 | 2018-Oct-19 | silo.whitte@abc.com |
|    1 | 2018-Jul-01 | pablo.gucci@abc.com |
|    6 | 2018-Aug-10 | pablo.gucci@abc.com |
|   13 | 2018-Oct-03 | pablo.gucci@abc.com |
+------+-------------+---------------------+

I wrote the following formula using FILTER function that then supplies the filtered dates per user to the DATEDIF function. However, i am not getting the desired result as shown above.

=ARRAYFORMULA(if(B2:B="","",1 + round(DATEDIF(min(sort(FILTER(B2:B,C2:C=C2:C),1,true)),sort(FILTER(B2:B,C2:C=C2:C),1,true),"D")/7)))

EDIT:

Formula Result:

Also removed SORT from above formula:

=ARRAYFORMULA(if(B2:B="","",1 + round(DATEDIF(min(sort(FILTER(B2:B,C2:C=C2:C),1,true)),FILTER(B2:B,C2:C=C2:C),"D")/7)))

Formula Result:

Both seem to work, but give unexpected results as MIN is evaluating to a single date 2018-Jul-01 instead of an Array of Minimum dates per user. Where am i going wrong?

score 0 · Answer 1 · answered Oct 19 '18 at 23:21

0

Did you try using the MINIFS() function ?

EG with the following

Dates in cells B2:B10 Emails in Cells C2:C10

The formula in cell A2 would give the earliest date for the email address in Cell C2

=MINIFS($B$2:$B$10, $C2:$C10, "="&C2)

You should be able to use this in your formula to calculate the number of weeks

answered Oct 19 '18 at 23:21

Ian Kenney

6,376
1
25
44

i am already using MIN in my formula. Where do i make the change? – sifar Oct 19 '18 at 23:39
MINIFS does not give me a list of minimum dates per user. Also getting error now `Function DATEDIF parameter 1 (8/30/2018) should be on or before Function DATEDIF parameter 2 (7/1/2018).` – sifar Oct 19 '18 at 23:56
Example https://docs.google.com/spreadsheets/d/1WI-DSjhexEv7J0cOSga1VBnkHgVjCQxES4SHciUgeI8/edit?usp=sharing – Ian Kenney Oct 20 '18 at 00:23
I understand you are using helper columns. What i need is a single array formula. – sifar Oct 20 '18 at 00:34
It is straightforward to combine the 'helper columns into one formula. – Ian Kenney Oct 20 '18 at 00:41
MINIFS (so does MIN) seems to return a single value instead of an array of minimum dates. – sifar Oct 20 '18 at 01:16
You can get the result of your stated problem by copying the formula to each row anyway, so not clear why you need to use `=ARRAYFORMULA()` – Ian Kenney Oct 20 '18 at 04:36
I am using an arrayformula as it robust and to avoid putting formulae in too many row cells in my sheet. – sifar Oct 20 '18 at 06:10

score 0 · Accepted Answer · answered Oct 20 '18 at 19:20

For those who might face a similar challenge, here is the answer:

=ARRAYFORMULA(IF(B2:B="","",ROUND((B2:B-VLOOKUP(C2:C,SORT({C2:C,B2:B},2,1),2,0))/7)+1))

The idea is to do a Vlookup on column C, passing it a switched and sorted range of dates in ascending order. These first dates are then deducted from the B column dates, to get the desired result ie. either days or weeks.

One can remove the +1 as i used it just to display the starting week as 1 instead of 0. So the result may differ slightly. But without +1, the result is accurate, especially if you are doing a Cohort.

As a check, i removed division by 7 and +1, then checked the days, which are correct.

Hope this helps.

Custom Week number from Acquistion Date of each User

2 Answers2