converting nested if statement into a set analysis

[#1]

Hi,

I need to create a set analysis which includes nested if statements. It's about the material price for the previous year, based on currency. It should be something like this:

=Sum({<YEAR_CODE = {$(vPY)}>} $(vActPrice))

where vPY = max(YEAR_CODE)-1

and

vActPrice = sum(if(CURRENCY = 'LOCAL', LOCAL_PRICE, IF(CURRENCY = 'USD', USD_PRICE, EUR_PRICE)))/ sum(QUANTITY)

How can I translate this formula?

Thank you very much

Cristina

[#2]

=Sum({<YEAR_CODE = {$(vPY)},CURRENCY = {$(vCURRENCY)} >} $(vActPrice))

Set up a variable vCURRENCY to be the currency variable

[tresesco]

May be like this?

(

Sum({<CURRENCY={'LOCAL'}>}LOCAL_PRICE) +

Sum({<CURRENCY={'USD'}>}USD_PRICE)+

Sum({<CURRENCY ={*} - {'LOCAL','USD'}>}EUR_PRICE)

) / Sum(QUANTITY)

[#4]

I'm not so sure whether I understood your solution. How should I set up vCurrency? We should consider that ActPrice points to many indicators on the basis of the currency value.

[#5]

Thanks for your reply, but using this formula I get different values from what I'm expecting. Moreover, I need to take into consideration the fact that I have another condition too (previous year) and I don't know how to put it together.

[tresesco]

Sorry, missed that. Try like:

(

Sum({<YEAR_CODE={"$(=(Max(YEAR_CODE)-1))"}, CURRENCY={'LOCAL'}>}LOCAL_PRICE) +

Sum({<YEAR_CODE={"$(=(Max(YEAR_CODE)-1))"}, CURRENCY={'USD'}>}USD_PRICE)+

Sum({<YEAR_CODE={"$(=(Max(YEAR_CODE)-1))"}, CURRENCY ={*} - {'LOCAL','USD'}>}EUR_PRICE)

) / Sum({<YEAR_CODE={"$(=(Max(YEAR_CODE)-1))">}QUANTITY)