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.

[swuehl]

Why do you need adavanced aggregation here?

I would try without advanced aggregation aggr() function, but if you need, try with NODISTINCT qualifier:

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

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

[gines_rueda]

NODISTINCT isn't working, escuse me but I haven't explained myself clearly.

I have amounts with serveral CCy's and what I need is the sum of the amounts in 2 consolidated currencies (GBP, USD).

Then I have a Rate to USD and Rate to GBP realted to the currencies to made that conversion.

So I need to multiply each amount for this rate depending on the dimension.

[tresesco]

Not sure if understood right.

It seems that, you are summing for all, may be it would be simpler :

pick

(

       match

       (

            [MainCcy],

            'USD',

            'GBP'

       ),

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

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

)

[swuehl]

Your dimension MainCcy is not related to the fact table in any way in your data model, right?

So you want to calculate the same sum of amounts in for both dimension values, just multiple with different rates at the end?

I am not sure I understand what you are trying to achieve, could you upload a small sample application with some mock up data and the desired result?

[gines_rueda]

Not exactly,

Lets say I have 10 amounts, 3 in EUR, 3 in USD and 4 in GBP.

And I have a relation between ISO Ccy and with [Rate to USD] and [Rate GBP].

So what I whant is the sum of the 10 amounts converted to USD in the first column and converted to GBP in the second.

In order to give you more information I have in the same table  [Amount] and [CCy ISO] fields, then I have another table with the Currencies information [CCy ISO], [Rate to USD], and [Rate to GBP].

And MainCCy is not relate with anything.

[gines_rueda]

I have tried this but it's getting me so much biger amount that I was expecting.

I think because is not multiplying correctly or doing the sum.

In order to give you more information I have in the same table  [Amount] and [CCy ISO] fields, then I have another table with the Currencies information [CCy ISO], [Rate to USD], and [Rate to GBP].

And MainCCy is not relate with anything.

[tresesco]

Make sure that [CCy ISO] field has unique values in Currency info table.

[gines_rueda]

Done, still the same erros in both approaches.

[tresesco]

Try to create a sample qvw and post, see: Preparing examples for Upload - Reduction and D... | Qlik Community

And explain your expected output there.