Hello, I am trying to calculate a rolling return rate over a set of data For example, in the dummy data attached, for every column starting in week 3, I would like to calculate the rolling 2-week return rate. * So for example, Company A, Week 3 = (Week 3) - (Week 1) / (Week 1) = (40-30)/30 = 33% * Company D, week 6 = (week 6) - (Week 4) / (Week 4) = (6-4)/4 = 50% I am okay with either replacing the cell with the new value, or building a new table with the rolling return rates. however, I have to do this for over 500 companies, for over 2 years (data in weeks), and calculate a 52-week rolling average... so would be great to find a non-tedious way to do this. Thank you very much for your help! TSR Example.yxmd

Rolling Multi-Field Calculation

Hello,

I am trying to calculate a rolling return rate over a set of data

For example, in the dummy data attached, for every column starting in week 3, I would like to calculate the rolling 2-week return rate.

So for example, Company A, Week 3 = (Week 3) - (Week 1) / (Week 1) = (40-30)/30 = 33%
Company D, week 6 = (week 6) - (Week 4) / (Week 4) = (6-4)/4 = 50%

I am okay with either replacing the cell with the new value, or building a new table with the rolling return rates.

however, I have to do this for over 500 companies, for over 2 years (data in weeks), and calculate a 52-week rolling average... so would be great to find a non-tedious way to do this.

Thank you very much for your help!

TSR Example.yxmd

Datasets

Accepted answers

RolandSchubert

Hi @yuvalshmul ,

you can use a Transpose tool to move weeks to rows, calculate the rolling return rate using a Multi-Row Formula tool and use a Cross Tab tool to move weeks back to columns.

I've attached a sample workflow, let me know if it works for you.