5 Replies Latest reply: Mar 18, 2013 4:17 AM by Thorsten Schröder

# Calculating with different Currency rates (sum in pivot)

Hello comunity,

I need help with my currency problem:

I have various currencies and I use a currency/rate table (ECB) to get the right values at the right day.

I now need to multiply my Amount field which also has a Curreny field with the right Currency and Date. This all works fine.

The problem I have is that when my currency is EUR I don't have any values in my ECB rate table, so I'll just use this if statement in my vEurAmount variable:

```if(Currency <>'EUR', sum(Amount / (1 * ECBrate)),sum(Amount))
```

In my straight table I can set the total mode to "Sum of all rows" but I can't use this in my pivot table.

So I tried this expression:

```=sum(aggr(sum(\$(vEurAmount))
```

which won't return anything.

I've tried other combinations but I can't get the syntax right.

Hope you can help me to set up the right expression.

BTW, is the variable I defined the right way to do this? I've also removed the sum function in the variable but no luck either.

thank you.

• ###### Re: Calculating with different Currency rates (sum in pivot)

HI,

Use Not Wildmatch.

`if( not Wildmatch(Currency,'EUR'), sum(Amount / (1 * ECBrate)),sum(Amount))`

When you use aggr fn then you should mention the field name to do the group by.

`=sum(aggr(sum(\$(vEurAmount)),FieldName))`

Regards,

Nirav Bhimani

• ###### Re: Calculating with different Currency rates (sum in pivot)

thanks for the expressions.

I still have the problem with my variable:

when I write

```=sum(aggr(sum(if(not Wildmatch(Currency,'EUR'), Amount / (1 * ECBrate),Amount)),Currency))
```

in my pivot expression, the right sum is calcluated.

How do I need to modify my variable `vEurAmount to use the expression you posted:`

`=sum(aggr(sum(\$(vEurAmount)),Currency))`

I get blank rows with or without the sum calculation in the variable.

• ###### Re: Calculating with different Currency rates (sum in pivot)

Hi

Regards,

Nirav Bhimani

• ###### Re: Calculating with different Currency rates (sum in pivot)

I'm working on real data so I can't create a file for it. BTW I changed the Currency field to InvoiceCurrency.

Let me try to explain it with this picture:

In the "EUR-Amount" pivot table I placed above expression which delivers the correct result for the total.

But since I need this expression in various ways, I need it in a variable.

In the "Point in Time Reporting" for example. The "Sales YTD" doesn't deliver the desired result. But I wasn't able to copy&paste the working expression above to replace the \$(vEurAmount) statement.

So I believe the variable expression needs to be changed.

• ###### Re: Calculating with different Currency rates (sum in pivot)

Since I can't get it to work like that I would like to create a new calculated field in my script for the EUR Amount.

this statement:

if(NOT Wildmatch(InvoiceCurrency,'EUR'), (Amount / (1 * ECBrate)),Amount) AS EUR_Amount

should create the new field. Problem I have is that the ECBrate field comes from a different table.

Both tables share a key field "CombCurrDate".

What do I need to do to get this calculation running?

thank you.