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

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

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

Developer

Developer Tools

Settings

Output

Batch Macro

Help

Interface Tools

Accepted answers

ivoller

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

CalcDeflated.yxmd

All comments

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

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

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

Cheers,

Iain

CalcDeflated.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335