Create Additional Columns with a value calculated on the previous column

[Bjoern]

Hej Everyone,

I am looking for some help or guidance on a problem creating multiple additional columns whose values are based on a formula that accesses the values of the previous column.

To be more specific: I have this data set of packUnits: 

Article	PackUnit	AvgGoodsIn	help_column	Week_1
1	48	10,2	1	37,8
2	48	25,9	1	22,1
3	48	42,6	1	5,42

I would like to "automatically add 25 additional columns, named: Week_2, Week_3, ....

For each of the new weeks the value needs to be calculated based on the values of the previous week. Formula is (e.g. for Week_2):

IF [AvgGoodsIn]<[Week_1] THEN [Week_1]-[AvgGoodsIn]

ELSE [PackUnit]*[help_column]-[AvgGoodsIn]+[week_1]

ENDIF

So, I end up for the next week with:

Article	PackUnit	AvgGoodsIn	help_column	Week_1	Week_2	…
1	48	10,2	1	37,8	27,6	…
2	48	25,9	1	22,1	44,2	…
3	48	42,6	1	5,4	10,8	…

I would need this for approx. 25 additional weeks. Somehow I am stucked here. Someone who knows an answer of a Thread dealing with the same problem? Could not find anything.

Thanks and kind regards,

Bjoern

[ntakeda · Accepted solution]

Hi, @Bjoern 

Step 1: Use the Generate Rows tool to create 25 rows vertically.
Step 2: Use the Multi-Row Formula tool to perform calculations based on the previous row.
Step 3: Use the Crosstab tool to transpose the data.

Please check the workflow for details.

[Bjoern]

@ntakeda 

Thanks a lot. Your way works just fine.

KR, Bjoern