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
Solved! Go to Solution.
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.
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
Thank you very much Ivoller, it works perfectly!
Cheers,
Flor