On multicurrency organization the calculations with currency fields are made like not currencies

[xhiena]

If I do a forecast and I have products in EUR and USD For example: 100 EUR and 100 USD (90.62 EUR) the SUM is 200 EUR instead 190.62 EUR

[afawcett]

Have you seen this #57?

[xhiena]

Yes I saw it, but is still calculating it wrong.

My case has a lot of currencies involved:

I have an object Sales product with a formula (currency) to calculate the price of 1 MB (I sell internet connections) and a lookup to a country custom object.

I want to do an average of the price of the MB in the countries.

My organization currency is EUR
Country: Chile with CurrencyISOCode: CLP (Chilean Peso)
The average of the price of the MB all the sales products in chile after the calculation is CLP 115771 (EUR 158.74)

But If I do the calculations in the developer console:

Select AVG( Price_per_MB__c), currencyisocode , count(id) from sales_product__c where country__c='a01b0000006H5I2' and service_status__c='Active' and ProductRecordTypeName__c != 'Hardware' group by currencyisocode

The results are:

Converting this quantities to CLP (Country currency)
103 CLF = 2766677 CLP 197.94 USD = 130804.86 CLP

Calculating the average:
(2766677 CLP * 48 + 130804.86 CLP * 69 ) / 117 = 1212188.30 CLP

The average should be 1212188.30 CLP instead the 115771 CLP the rollup calculates

[afawcett]

Thanks for the detail here, i'll be sure to do it justice when i can focus on it, hopefully soon! 👍

[afawcett]

Sorry for the delay on this one, i've flagged it as a priority item to review.

[afawcett]

Hi @xhiena, i've been staring at this again. I have myself encountered issues where the SOQL functions don't work as per my own hand calculations. The differences have been minor compared to yours though. In the end Salesforce support did convince me, in my case, it was supported and and aspect of how internally aggregate queries and currency conversion works.

It seems like you have boiled this down to the specific SOQL, which is basically what the tool is doing. So i'm not sure what else the tool could do to help if the platform is not returning the figures you expect.

My only advice, is to raise a case with Salesforce Support and provide your explanation above, and like me hopefully one way or another, you will at least perhaps get some kind of explanation from them on the logic behind it, which may or may not help you. Sorry i cannot do anything more here and also for the huge delay in getting back to you.

Good luck! 👍