topic Re: Re: set analysis (sum, avg, aggregation...) in QlikView

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

Mon, 28 Apr 2014 14:58:34 GMT

Hi all,

I have a list of orders like the following :

Order ID	Amount	Currency	Currency Rate	Order 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...)

alexandros17 — Mon, 28 Apr 2014 15:04:24 GMT

Try this

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

Let me know

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

Mon, 28 Apr 2014 15:08:40 GMT

thank you but this not works

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

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

Mon, 28 Apr 2014 15:09:57 GMT

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

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

alexandros17 — Mon, 28 Apr 2014 15:10:00 GMT

could you post your document?

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

PrashantSangle — Mon, 28 Apr 2014 15:23:35 GMT

Hi,

did you try with TOTAL Keyword,

Like this,

Avg(TOTAL CurrencyRate)

Regards,

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

Mon, 28 Apr 2014 15:30:23 GMT

attached an example of data

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

Mon, 28 Apr 2014 15:37:06 GMT

doesn't work

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

alexandros17 — Mon, 28 Apr 2014 15:40:57 GMT

Here it is my example, let me know

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

Mon, 28 Apr 2014 16:01:33 GMT

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...)

jaimeaguilar — Mon, 28 Apr 2014 16:09:37 GMT

Hi,

maybe like this:

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

regards

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

MK_QSL — Mon, 28 Apr 2014 16:16:16 GMT

Try

AVG(TOTAL <Currency> Currency_Rate)

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