7 Week Average

[Anweinbe]

Hi all, good morning!

I have data similar to what is attached, where I have a product as a row, and columns are my week end dates. I wanted to create a field that average the last 5 of the most recent dates. The problem is as I bring in new data, the most recent date headers will change. Is there a way to build a formula that is based on column order rather than a name?

As an FYI, I created the transpose in Alteryx. Prior to the transpose I have the data in Alteryx in a summarization. See tab 2 for that data in case its easier to work with that.

What would be even better is just a rolling 5 period average if that was possible. I am trying to determine sales trends.

Thanks so much!

[Raj · Accepted solution]

@Anweinbe 
find the workflow attached.

mark done if solved.

[Anweinbe]

@Raj  I think this is close. My actual dataset has different products. Is there something we can add so that way when a new product appears it doesn't take values from a different product?

[Raj]

@Anweinbe just check on group by product in multirow formula tool.

hope this helps.

let me know if anything else.