topic Avg () function with percentile in QlikView

Avg () function with percentile

Anonymous — Thu, 28 Oct 2010 14:13:53 GMT

Hi everybody,

I'm with a difficulty that I counldn't solve myself.

At a finantial analysis we have a metric called Makup that it's basically Expenses + Taxes / Incomes. This one I got it, but we need to know the average markup until now and that's when my problem begin.

The avg () function doesn't work.

I tried to do it manually (sum every result and divide by the number os months we have) but it's still gets no results.


 sum
/*expenses*/
(sum ({<grup_neg = {'Custo Fixo'}> } valor ) *-1
/*taxes*/
((sum ({<grp_conta = {'Impostos s/receita'}>} valor)
+
sum ({<grp_conta = {'Despesa'}, grup_neg = {'Impostos'}>} valor)) *-1)
/
/*Incomes*/
sum ({<grp_conta = {'Receita'}, prevXfat = {'REALIZADO'}> } valor ) *-1)

Could anyone help me? What am I doing wrong?

Best Regards,

Leandra Scordamaglia

Avg () function with percentile

Thu, 28 Oct 2010 14:58:03 GMT

You can't do nested aggregates [Sum(Sum(Sales))] without the Aggr() function.

Do you need the Sum around everything? Also, why are you multiplying the numerator and denomonator by -1? If you do a little algebra on that expression, the negative ones cancel out, so I believe you should be able to get rid of them. Also, there is no operator between the expenses and taxes on your expression. The lack of an operator and nested aggregates are the reason you're not getting any values.

You should be able to do (simplified):

(Sum(Expenses) + Sum(Taxes)) / Sum(Incomes)

Replace the Sum() with the actual expressions for each.

Avg () function with percentile

Anonymous — Thu, 28 Oct 2010 15:13:45 GMT

Ok, i got your point.

I need to sum everything because I have some restrictions to get the values and it's not a only value its several values thats composes the result, for example I have several expenses in january and a have to sum all of them to get the total expenses of january.

And how to do the avg() function in this case?

thanks,

Avg () function with percentile

Thu, 28 Oct 2010 15:31:41 GMT

If the simple version won't work for you, then you're going to need to use Aggr(). Aggr() is a complex, but powerful function. First, you'll need your working Makup expression. Could you post that? Then you will need the dimensions that you will be aggregating across. This is where is gets difficult. It sounds like Month may be a requirement and you usually want anything that is a dimension in your chart. Which dimensions you need in the Aggr() function really depends on your dataset. It's difficult to troubleshoot this one without seeing the structure of the data.

The basic setup for your Aggr() function will be:

Avg(Aggr(Makup Expressions Here, Dimension1, Dimension2, Dimension3....))

You need at least one Dimension, but can use as many as you need. Without a sample to go by, it's going to be difficult to give a more precise answer.