I have a dataset which holds transaction data. I have dates, i have derived a week column from date which has values like “2024/17”, etc. and I usually filter data based on weeks. My use case is when I select a week using 2 variables- one will pick year and other will pick week number, I want to subtract lets say past 4 weeks (lookback period) of data and see rest of the previous data in my card.
For example: If I pick 2024/17 in the variable control, it should subtract past 4 weeks which is 2024/13 and show the rest of the data from 2024/13 and back.(it can go back to any data). Similarly, if I pick 2023/16, It should subtract 4 weeks and show rest of the previous data.
However, I am not able to think of how I can execute this in a beastmode with variables.
The reason for doing this is I know that if a product is sold 1 week ago it still has 3 weeks to be returned. So the return rate for that product will be very less now. But if I go back in time already i.e. 4 weeks, I can now see the return rate for the product whose return window is already completed.
Any help would be appreciated. Thanks a lot! Please let me know in case of question.