Nested if statement

[vilstrup]

Hi,

I am trying to make and if statement in a chart expression with the following issues:

sum(

If(DATAAREAID_Trans='rme',COSTAMOUNTPOSTED)

If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'USD'

If(DATAAREAID_Trans='sol',COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'PLN'

If(DATAAREAID_Trans='rtk',COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'EUR'

)

Can anybody help me with this?

Kind regards Nicolai

[jaumecf23]

Try this:

sum(

If(DATAAREAID_Trans='rme',COSTAMOUNTPOSTED)

If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED*(only({<EXCHRATE ={USD}>}EXCHRATE)/100)

If(DATAAREAID_Trans='sol',COSTAMOUNTPOSTED*(only({<EXCHRATE ={PLN}>}EXCHRATE)/100)

If(DATAAREAID_Trans='rtk',COSTAMOUNTPOSTED*(only({<EXCHRATE ={EUR}>}EXCHRATE)/100)

)

[vilstrup]

Hi Jaume,

it seems there is something not right ?

[jaumecf23]

Try this:

If(DATAAREAID_Trans='rme',sum(COSTAMOUNTPOSTED),

If(DATAAREAID_Trans='sdx',sum(COSTAMOUNTPOSTED)*(only({<EXCHRATE ={USD}>}EXCHRATE )/100),

If(DATAAREAID_Trans='sol',sum(COSTAMOUNTPOSTED)*(only({<EXCHRATE ={PLN}>}EXCHRATE )/100),

If(DATAAREAID_Trans='rtk',sum(COSTAMOUNTPOSTED)*(only({<EXCHRATE ={EUR}>}EXCHRATE )/100),

))))

What I see in your original post. Is the Currency name and the currency value has the same field name : EXCHRATE . I expect that you should have two different field names for these values.

[vishsaggi]

Try this may be:

= sum(

If(DATAAREAID_Trans='rme',COSTAMOUNTPOSTED,

If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED*(only({<EXCHRATE ={USD}>}EXCHRATE)/100),

If(DATAAREAID_Trans='sol',COSTAMOUNTPOSTED*(only({<EXCHRATE ={PLN}>}EXCHRATE)/100),

If(DATAAREAID_Trans='rtk',COSTAMOUNTPOSTED*(only({<EXCHRATE ={EUR}>}EXCHRATE)/100), 0))))

)

Sorry above expr does not work completely forgot about Only() function used. You have to use Aggr if you have two functions. Like sum() and Only

So probably you want o add some dimensions in your aggr() like

= Sum(Aggr(Sum(yourIfConditions), Dim1))

[vilstrup]

Hi Jaume,

Thank you very much for taking your time to help on this matter.

Actually i have a an Exchange table with 3 fields.

FROMDATE, EXCHRATE and CURRENCYCODE.So i just realised that it should be something more like this:

If(DATAAREAID_Trans='rme',sum(COSTAMOUNTPOSTED),

If(DATAAREAID_Trans='sdx',sum(COSTAMOUNTPOSTED)*(only({<CURRENCYCODE ={USD}>}EXCHRATE )/100),

If(DATAAREAID_Trans='sol',sum(COSTAMOUNTPOSTED)*(only({<CURRENCYCODE ={PLN}>}EXCHRATE )/100),

If(DATAAREAID_Trans='rtk',sum(COSTAMOUNTPOSTED)*(only({<CURRENCYCODE ={EUR}>}EXCHRATE )/100),

))))

It doesn't work though. it only works for the first if statement where its just COSTAMOUNTPOSTED?

- Nicolai

[jaumecf23]

Is it possible to you to attach the qvw file? I will try to find a solution.

[jaumecf23 · Accepted solution]

Try this new Expression:

sum({<DATAAREAID_Trans={rme}>}COSTAMOUNTPOSTED)+

sum({<DATAAREAID_Trans={sdx}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={USD}>}EXCHRATE /100),0)+

sum({<DATAAREAID_Trans={sol}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={PLN}>}EXCHRATE /100),0)+

sum({<DATAAREAID_Trans={rtk}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={EUR}>}EXCHRATE /100),0)+

sum({<DATAAREAID_Trans={bsl}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={CFH}>}EXCHRATE /100),0)

[vilstrup]

Thank you so much Jaume,

it finally works!

Regards Nicolai