topic How to calculate Average figures? in QlikView

How to calculate Average figures?

Fri, 11 Nov 2011 05:38:28 GMT

Hi All,

I have the following table:

load * Inline
[
Region, ExchangeRate,LocalAmount
US, 1
Brazil,1.7
US,,100
Brazil,,100

]

I concatenate the ExchangeRate and LocalAmount into one table.

Then ExchangeRate is based on USD.

Now I want to show the total USD Amount in one pivot table.

But when I use this expression sum(LocalAmount)/Avg(ExchangeRate), but it show the wrong numbers.

However, if I choose one region US or Brazil, the number is right, so how to calculate this total USD number?

I have attached the QVW doc.

Thanks.

Re: How to calculate Average figures?

Fri, 11 Nov 2011 06:17:44 GMT

I might be misunderstanding here (specially the average portion).

From what I understood you have following need:

1. You have atleast two regions i.e. US and Brazil

2. You have the exchange rate. (1 for US and 1.7 for Brazil)

3. You have amount in local currency that was earned (since it is +ve) for the region.

4. Now you need to get the amount for the region in US dollars. (LocalAmount/ExchangeRate)

Try with straight table first:

1. Create a straight table.

2. Set dimension as Region.

3. Expression 1 = Sum(ExchangeRate), Expression 2 = Sum(LocalAmount), Expression 3 = column(2) /column(1), Expression 4 = Sum(LocalAmount) / Sum(ExchangeRate)

For Pivot:

1. Create the pivot chart.

2. Dimension is Region.

3. Expression is Sum(LocalAmount) / Sum(ExchangeRate)

What I dont understand is the reason for average.

Re: How to calculate Average figures?

Fri, 11 Nov 2011 06:21:01 GMT

Thanks for your reply, why I use average exchangeRate, because there are lot of ExchangeRate based on day, I just

List one exchage rate record for each country.

Re: How to calculate Average figures?

Fri, 11 Nov 2011 06:23:30 GMT

Another thing is: Even if I use your expression:

Sum(LocalAmount) / Sum(ExchangeRate), the result is also wrong.

For example:

If you choose Region BR and US together: the figure is: 74, the right number is: 158

How to figure it out?

How to calculate Average figures?

Fri, 11 Nov 2011 06:40:02 GMT

Shows me right in my straight table. Also in the expressions tab i had to change the Total mode to Sum of Rows.

Also can u set dimension as region in your chart?

If I am still misunderstanding can u post the latest file again?

Best Regards,
Vishal

Re: How to calculate Average figures?

Fri, 11 Nov 2011 06:45:03 GMT

I can not add Region dimension, I just want to show the total USD amount in Pivot table not straight table.

How to calculate Average figures?

Fri, 11 Nov 2011 06:53:31 GMT

Here you go: sum(aggr(Sum(LocalAmount)/Avg(ExchangeRate),Region))

In your original file the calc that was being done was 200 / ((1+1.7)/2) = 148.148. What is actually needed is (100/1) + (100/1.7) = 158.82.

I hope this helps. Let me know if it does not.

Best Regards,
Vishal