past N rolling weeks from max week

[jcardoso]

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,

[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/t...

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 · Accepted solution]

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.

[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!