Question:
Hi,
Can we get value from variable (i.e. year) or something in the formula editor in the parameter group ouf function type FORMULA? I need to make the same calculations for each characteristic in the data column. Here's the situation:
Key Figure Year 2003 Actual Year 2004 Budget
Sales Volume 100 200
Sales Price 10 20
Sales Revenue S.Vol. * S.Prc. S.Vol. * S.Prc.
Expense 1 10 30
Expense 2 20 40
Total Expense Exp.1 + Exp.2 Exp.1 + Exp.2
Lead Column: Key Figure
Data Column: Year, Version
Currently, I use the FORMULA function and create the same formula for each operand {Key Figure,Year,version}. And also, I have to manually entery the value of the year....if the year changes then the formula has to be remodified.
{sales_rev,2003,A} = {sales_vol,2003,A} * {sales_prc.2003,A}
{sales_rev,2004,B} = {sales_vol,2004,B} * {sales_prc.2004,B}
It's a double job....which is not very efficient way to do if there're so many figures to be calculated. Is there a way which could calculate them all at once using a formula for each key figure....or maybe using other functions?
Please help....I'm pretty new on BPS.....so pls bare with me if my questions might sound pretty stupid or unrealistic....=).
THank you so much...
Answer:
If you do not include year or version in "Fields to be changed" (i.e. only include key figure), your formula would simply become {sales_rev} = {sales_vol} * {sales_prc}. The function would automatically loop through all the matching values in year and version, and calculate sales_rev for all the value pairs.
_________________
CF