11 Replies Latest reply: Apr 28, 2014 12:16 PM by Manish Kachhia

# set analysis (sum, avg, aggregation...)

Hi all,

I have a list of orders like the following :

Order IDAmountCurrencyCurrency RateOrder Date

I need to show, in a text box, the sum of all Amount in Euro where :

sum of all Amount in Euro =sum of all orders and for every Order: sum(Amount) * the average of Currency Rate of the current Currency.

that means, the average of Currency is calculated based on all other order having the same currency

any idea ?

Regards,

Yacine

• ###### Re: set analysis (sum, avg, aggregation...)

Try this

Sum(Aggr(sum(Amount) * Avg(CurrencyRate), OrderNo))

Let me know

• ###### Re: set analysis (sum, avg, aggregation...)

thank you but this not works

in your expression, Avg(CurrencyRate) is equal to CurrencyRate

• ###### Re: set analysis (sum, avg, aggregation...)

I think we should add some conditions to Avg(CurrencyRate)

• ###### Re: set analysis (sum, avg, aggregation...)

Hi,

did you try with TOTAL Keyword,

Like this,

Avg(TOTAL CurrencyRate)

Regards,

• ###### Re: Re: set analysis (sum, avg, aggregation...)

doesn't work

• ###### Re: set analysis (sum, avg, aggregation...)

Hi,

maybe like this:

sum(Amount)*sum(aggr(avg(Currency_Rate), OrderID, Currency))

regards

• ###### Re: set analysis (sum, avg, aggregation...)

Here it is my example, let me know

• ###### Re: Re: set analysis (sum, avg, aggregation...)

thank you but this is not what I want. for this example, we should have 1 currency rate for CHF and 1 currency rate for BRL (for all the data). then, we have to divide every order amount by the currency rate

• ###### Re: set analysis (sum, avg, aggregation...)

Try

AVG(TOTAL <Currency> Currency_Rate)

&

SUM(Amount)*AVG(TOTAL <Currency> Currency_Rate)