Forum Discussion
Dynamic Count of Week between two dimensions
Hi all,
I have the challenge below whereas the column DesiredResult needs to count week number from "SaleDate" within the dimension "Event" and based on "OpenDate" as start Point
and it starts counting number of weeks again for the next event in the below case event B
For example, SalesDate for A is 01/01/2023 that's week 1 of that event
and SalesDate for event A on 25/02/2023 is week 2 for that event as DesiredResult
| Event | OpenDate | SaleDate | DesiredResult | |
| A | 1/01/2023 | 1/01/2023 | 1 | |
| A | 1/01/2023 | 4/01/2023 | 1 | |
| A | 1/01/2023 | 15/01/2023 | 3 | |
| A | 1/01/2023 | 5/02/2023 | 4 | |
| A | 1/01/2023 | 25/02/2023 | 8 | |
| B | 1/03/2023 | 1/03/2023 | 1 | |
| B | 1/03/2023 | 15/03/2023 | 3 | |
| B | 1/03/2023 | 25/03/2023 | 4 | |
| B | 1/03/2023 | 5/04/2023 | 5 | |
| B | 1/03/2023 | 30/04/2023 | 9 |
However for event B openDate is 01/03/2023 therefore saleDate on 30/04/2023 is week 9 of that event b
Any ideas?
Many Thanks in Advance
Regards,
Hi DataFab2023 ,
What is your logic for counting how many weeks there are between two dates? Do you get the difference between the two and divide it by seven? Also, and as always, please post a sample data that we can copy-paste (not an image).
8 Replies
Hi DataFab2023 ,
What is your logic for counting how many weeks there are between two dates? Do you get the difference between the two and divide it by seven? Also, and as always, please post a sample data that we can copy-paste (not an image).
Hi,
thanks for taking the time to reply me.
I have edit the original post with the data you can copy.
so the logic is basically each event A, B will have it's own start of week number until it finishes
for example event A can have X number of weeks however it starts always with 1 based on OpenDate
same for event B and so on...
hi DataFab2023 ,
I understand that the week count is to be calculated for each even but which columns are used for calculations? Is just OpenDate vs SalesDate in the same row or need to access the previous SalesDate? Once the number of days has been calculated, how do you then translate that to a week count?
