Calculating with different Currency rates (sum in pivot)

[#1]

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.

[nirav_bhimani]

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

[#3]

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.

[nirav_bhimani]

Hi

Can you please share your Demo file so that its more clear, Will help you.

Regards,

Nirav Bhimani

[#5]

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.

[#6]

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.