Average by dimension

Fri, 10 Oct 2014 12:06:22 GMT

Hello again,

here is my problem

In this table:

for the colum circled in red, I need to take the mean of some values.

The first dimension is the commercial, the second dimension the customer and the third dimension the order number. The last dimension is not usefull and will be deleted.

For my statistics, I need to take the average of the quotes (total amounts of quotes / number of quotes) which will be written as the amount in € of the order.

After that I need to take the average of the orders per customer (total amount in € of the MEANS of the orders / amount of orders) as the mean value per customer.

Finally I need to take the average of all the customers (total amount in € of the MEANS of customets / amount of customers).

Until now I have this expression for that column:

avg(aggr(sum(aggr(if(count({<JD_DATE=, JD_NDEVIS=P(JD_NDEVIS), JD_NVARIANT_FABR={1}>} JD_CODUNIQ) = count ({<JD_DATE=, JD_NDEVIS=P(JD_NDEVIS)>} JD_CODUNIQ),

avg({<JD_NVARIANT_FABR={1}, JD_DATE=, JD_NDEVIS=P(JD_NDEVIS)>}JD_PX_TOTAL),

avg({<JD_NVARIANT_COMM={1},JD_DATE=, JD_NDEVIS=P(JD_NDEVIS)>}JD_PX_TOTAL)), JD_NDEVIS)), JD_NDEVIS))

The condition for the if is not important for the understanding of the problem and the trued anf false value either.

JD_NVARIANT= quote of the order

JD_NDEVIS= order number

JD_PX_TOTAL= value of the quote

The problem is that in this table, the mean per order is correct, and also per customer, but when it comes to the mean of the commercial, it doesn't calculate it correctly. What it does now, is taking all the orders values and dividing them by the number of orders instead of taking all the values of the customers and divide it by the number of customers.

Does anyone see what's wring in my expression? I tried several things with avg, sum, aggr etc 😕

Re: Average by dimension

Mon, 13 Oct 2014 06:53:23 GMT

Do you know if there is a way of putting an expression for each dimension. For example for dimension 1, calculate the average on this way, for dimension 2 on this way etc?

topic Re: Average by dimension in QlikView

Average by dimension

Re: Average by dimension