topic avg and if function in one formular in QlikView

avg and if function in one formular

Tue, 04 Jun 2013 11:01:37 GMT

Hello together,

i have an formula of two things, the one thing is the avg formula and the other thing is the if condition:

AVG({<[Undertenthousand]>} [Anz Tage])

This formula calculate the average of the column "Undertenthousand" of the days.

if(Währung <> 'EUR', EffektwertSumme*Kurs, EffektwertSumme)

This formula look, if the currency is not EUR, then multiple Effektwert with Kurs, otherwise take EffektwertSumme

Do you know, how can I cobinate this two formulas in one formula.

Thank you.

Nikolaus80

Re: avg and if function in one formular

sushil353 — Tue, 04 Jun 2013 11:09:52 GMT

Hi,

your requirement is not clear.. do you want to integrate the AVG() function inside the if condition.?

then try this:

if(Währung <> 'EUR', EffektwertSumme*Kurs, AVG({<[Undertenthousand]>} [Anz Tage]))

Re: avg and if function in one formular

Tue, 04 Jun 2013 11:47:39 GMT

no, that is not the right solutions.

Here a better introduction to my problem:

I have the problem that I have different currencies in the column "Währung" like US Dollar. In the other hand I have a colum for the currency rate "Kurs".

The formel without the currency is "Avg({<[Undertenthousand]>}[Anz Tage])"

The column "Undertenthousand" is a if condition in the load script

"if(Effektsumme<'10000', EffektSumme) as Undertenthousand"

The if condition shows the Effektsumme under thousand and save it in Undertenthosuand.

Now I want to include the currency in the formula, that caculate, if there is no "EUR" then calculate the currency with rate, that i have the amount in euros.

Next step is that this calculated amount (currency*rate) should if it under ten thousand Euros to save as "Undertenthousand"

I hope is better to understand.