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:
1
7
7
7
8
10
10
13
13
14
16
16
16
17
19
22
27
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:
10
10
19
7
7
13
22
27
8
13
16
16
16
17
1
7
14
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?
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.
0
0
10
0
0
6
15
21
0
5
7
7
8
9
0
6
13
As a check, i removed division by 7 and +1, then checked the days, which are correct.
0
0
69
0
0
42
106
144
0
34
52
52
55
60
0
40
94
Hope this helps.