topic Re: Average Calculation in QlikView

Average Calculation

Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I am trying to get the average value for my currency conversion,

Here is the logic I am trying to apply in QlikView,

Set vCurrencyEUR = select avg(currency_ rate) where Reimburse_Cur=EUR and Spend_Cur =USD

Set vCurrencyINR = select avg(currency_ rate)where Reimburse_Cur=INR and Spend_Cur=USD

Please help me how to convert this into working QlikView scripts.

sergio0592 — Wed, 12 Jul 2017 18:12:43 GMT

Try with as expression :

=avg( {<Reimburse_Cur={"EUR"}, Spend_Cur ={"USD"} >} currency_ rate)

=avg( {<Reimburse_Cur={"INR"}, Spend_Cur ={"USD"} >} currency_ rate)

But Set expression not worked in the load script. You must be in front end.

Anonymous — Wed, 12 Jul 2017 19:36:04 GMT

You would need to build a summary table. Example for first one.

EURCurrency:

Load

avg(currency_rate) as AvgCurrencyRate

Resident YourDataTable

where Reimburse_Cur='EUR' and Spend_Cur='USD';

let vCurrencyEUR= peek('AvgCurrencyRate');

Drop table EURCurrency;

Fri, 14 Jul 2017 07:43:21 GMT

Thank you Jean and Wallo for your suggestions. Let me work on both suggestions and will get back to you