Search values in the same table with a condition creating new column

[Florfbonfran]

Hello,

We have the next problem. We have to calculate the formula DEFLATED witch is TON*PY TO/PY TON.

PY = Previous year

So we need to go down on the table and look for the same period and the same BFPP of the previous year and catch the TO and the TON.

If I take the first line that has year 2018, month 7, BFPP AA1, we have to look for the mounth 7 of the 2017 year. And bring TO = 0,1 and TON = 2. So we can calculate 13 * 0,1 / 2 in the DEFLATED column.

BFPP	MES	ANIO	EJ FISCAL	PLAN	FECHA	TO	TON	DEFLATED
AA1	7	2018	2018	SOP JUN	31/07/2018	0,6	13
AA2	8	2018	2018	SOP JUN	31/08/2018	0,4	19
AA3	9	2018	2018	SOP JUN	30/08/2018	0,2	20
AA1	7	2019	2018	SOP JUN	31/07/2019	0,7	40
AA2	8	2019	2018	SOP JUN	31/08/2019	0,4	81
AA3	9	2019	2018	SOP JUN	30/08/2019	0,6	9
AA1	7	2017	2018	ACTUAL	31/07/2017	0,1	2
AA2	8	2017	2018	ACTUAL	31/08/2017	0,2	10
AA3	9	2017	2018	ACTUAL	30/08/2017	0,3	7
AA1	7	2016	2018	ACTUAL	31/07/2016	0,7	8
AA2	8	2016	2018	ACTUAL	31/08/2016	0,8	20
AA3	9	2016	2018	ACTUAL	30/08/2016	0,1	10

How could we go down and look for values with a condition?

Thank you.

Regards,

Florencia

[ivoller]

Are you just comparing plans vs actuals? I.e. When you get to row 7 (MES = 7, ANIO = 2018, BFPP = AA1) will you wnat the TON for that row (2) * TO/TON for row 10? If yes, what should the calculation be when you get to ANIO = 2016 and there isn't a prior year?

Sorry for all the questions. Just want to be sure I understand the problem before offering my 2 cents.

[ivoller · Accepted solution]

Depending on the specifics, the attached workflow or something similar may work.

Basically just set up a key for the current record and for the equivalent from last year and then join the data by PY key to key and do teh appropriate calculation. I had to manipulate the decimal format.

There's other ways to go about this (join on multiple fields, transpose the data, multi row formula etc)

Cheers,

Iain

[Florfbonfran]

Thank you very much Ivoller, it works perfectly!

Cheers,

Flor