past N rolling weeks from max week

Hi,

i'm struggling converting week-year to dates using Alteryx only, we need a macro or workflow that, given the current table, creates the desired table, as per below, giving the N last weeks for each of the entries

N is the number of rolling weeks back. If N is different than each id would have N number of rows.
Probably best if It will find the most recent week and build the logic from there.

Thank you so much for any help,

Regards,

week-year.yxmd

week-year.png

Transformation

Preparation

Accepted answers

jamielaird

Hi @jcardoso ,

Here's a solution that gets you most of the way there.

There is a discrepancy with a few of the weeks which shouldn't be too difficult to resolve with a better understanding of the ISO date format and the %U function. I don't have time to finish it up right now, but posting my work so far in case this helps.

N Previous Weeks.yxmd

All comments

AngelosPachis

Hi @jcardoso ,

A good place to start is reading this post which talks you through how to convert the week number - year to a date based on the ISO format

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Exact-week-number-conversion-to-date/td-p/82940

Then you only need to use a generate rows tool that will generate N(your variable) weeks in the past, which will essentially subtract 7 days from the dates you calculated above.

Hope that helps,

Angelos

DawnDuong

hi @jcardoso

You can try using an iterative macro to take in the N input in order to do the lookback.

and another macro to go through each line that you want to do the look back for.

The date conversion should be well explained in the link shared by @AngelosPachis above, although from the "Sample output" you do not seem to need conversion from String to date output?.

Dawn.

jamielaird

Hi @jcardoso ,

Here's a solution that gets you most of the way there.

N Previous Weeks.yxmd

jcardoso

Tried you approach but no luck so, I've cheated! Used the .py tool to get it, it just much simpler!

s = week_tbl(1,'W22-2021',5)

Macro attached!

Thanks for all you help!

RollingNWeeks.yxmc

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703