Multiple steps calculation

[sspi1153]

Hey there!

I have 3 rows of data. 

First one is time - let´s say I am interested in a 5 years time frame. 

Second and third are variables - X and Y

=> =>=> Data looks like =>=>=>

 Period      X         Y 

 1              0.9       1.1            

 2              0.5        1

 3              2           1.3

 4              4           0.3

 5              2.2        2.1

Calculation for the year one is prety simple.  Ijust need to summarize values  X*Y for all periods (1-5) =>(0,9*1.1)+(0.5*1)+(2*1.3)+(4*0,3)+(2.2*21).

Here comes the tricky part. 

In period 2 I want to skip value of X in period 1 and value of Y for period 5. Calculation should looks like => (0,5*1,1)+(2*1)+(4*1,3)+(2,2*0,3)

In period 3 I want to skip values of X for periods 1 and 2 and values of Y for periods 4 and 5. Calculation should looks like => (2*1.1)+(4*1)+(2*1,3)

Thanks and cheers!

[Thableaus · Accepted solution]

Hi @sspi1153 

Use Multi-Row formula Tool and you'll be fine.

[X] * [Row-1:Y] - Period 2

[X] * [Row-2:Y] - Period 3

Then Summarize Tool will do the rest.

Cheers,

[danilang]

Hi @sspi1153 

Here's a workflow that lays out the concept that @Thableaus was getting at.  It relies on the fact that rows that don't exist, i.e. any rows before 1 are treated as having a value of 0, so multiplication by these gives you a 0 result.

The window with the Blue title bar shows this concept.  Period2 and Period3 have 0 values according to your requirements.  The other window shows the result of the summation.

This method is not at all dynamic and is a maintenance headache if you need to add more periods.

Dan

[sspi1153]

Thanks, I got this one.

But what would be the solution if I would like to make it dynamic? Where should I start looking for the solution?

Thanks!

[danilang]

@sspi1153 

It all depends on what you mean by dynamic.  If it's just a questions of the number of periods increasing then the workflow will work.  If the formulas need to be dynamic as well, i.e. period n looks n-x rows back or some other definable rule, then you'll probably be looking at a macro solution with a dynamic multi row formula or perhaps one that joins the X and Y columns with a dynamic offset.

Dan

[estherb47]

Hi @sspi1153 

Attached is a workflow with an iterative macro. It will update to however many rows your data has.

Please let me know if this works.

Under the hood: 

With each pass, the X value from the row below is moved up. When the tool has run through all of the rows, it's finished.

<<NOTE ON UPDATED POST: workflow no longer needs the filter (the macro still does). After I posted the picture, I realized that the > should be a >=. Whoops!!>>

Cheers!

Esther

[sspi1153]

Thanks!