topic converting nested if statement into a set analysis in QlikView

converting nested if statement into a set analysis

Mon, 23 Sep 2013 09:49:09 GMT

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

Re: converting nested if statement into a set analysis

Mon, 23 Sep 2013 10:28:26 GMT

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

Set up a variable vCURRENCY to be the currency variable

Re: converting nested if statement into a set analysis

tresB — Mon, 23 Sep 2013 10:44:48 GMT

May be like this?

(

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

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

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

) / Sum(QUANTITY)

Re: converting nested if statement into a set analysis

Mon, 23 Sep 2013 12:04:33 GMT

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.

Re: converting nested if statement into a set analysis

Mon, 23 Sep 2013 13:40:53 GMT

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.

Re: converting nested if statement into a set analysis

tresB — Mon, 23 Sep 2013 15:53:32 GMT

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)