Expression error using an if/pick match with 2 aggr cases

[gines_rueda]

Hi,

I have a table with:

Dimension: [MainCcy] (has 2 values 'USD', 'GBP')

Expression:

pick

(

       match

       (

            [MainCcy],

            'USD',

            'GBP'

       ),

       SUM(AGGR(SUM([Amount]), [Ccy ISO]) * [Rate To USD]),

       SUM(AGGR(SUM([Amount]), [Ccy ISO]) * [Rate To GBP])

)

[Amount] is related with [Ccy ISO] and  [Rate To GBP]/[Rate To USD] but no with [MainCcy] in the data model.

If I replace the sum aggr funtions for numbers or justs sums it works, but the AGGR funtions produce something that only the formula for the first MainCcy is being shown the second formula in the column is empty.

Do you know why this is happening?

Could you give me a solution?

Thanks.

Ginés.

[PrashantSangle]

Hi,

What is your dimension?

Try this,

If(wildmatch ([MainCcy], 'USD', 'GBP'),

     AGGR(SUM([Ccy ISO]) * [Rate To USD]),Dimension_Field),

     AGGR(SUM([Ccy ISO]) * [Rate To GBP]),Dimension_Field) 

)

You dimension_field must contain unique value.

Regards

[gines_rueda]

Ok, I think you have gave me the key.

The Ccy ISO field is into a $Syn table with some other fields, if I change the Ccy ISO to be unique for the amount table and the currency dimension it works.

So that means it's getting duplicates from that automatically created $Syn table.

Do you know a way to keeping that $Syn table, avoid the duplication?

Thanks.

[gines_rueda]

H Max,

The thing is I have thin Ccy ISO field inside a $Syn table with anothers fields (account ids, and other keys).

So as tresesco said this works but only if I removed the Ccy ISO field from the $Syn table:

pick

(

       match

       (

            [MainCcy],

            'USD',

            'GBP'

       ),

       SUM([Amount] * [Rate To USD]),

       SUM([Amount] * [Rate To GBP])

)

So basically what I need is an expression that avoids that Ccy ISO virtual duplication inside the $Syn table.

Could you help me with that?

Thanks.

Ginés.