topic Need help in calculation in QlikView

Need help in calculation

Tue, 22 Dec 2009 00:55:11 GMT

Hello all!

We have two tables (see attached qvw):

Dates:
LOAD * INLINE
[date_c, month
2009-11-1, 11
2009-11-1, 11
2009-11-2, 11
2009-12-1, 12
2009-12-1, 12
];
Orders:
LOAD * INLINE
[date_c, customer, ordercnt
2009-11-1, first, 1
2009-11-1, second, 1
2009-11-2, first, 1
2009-12-1, first, 1
2009-12-1, second, 1
];

And we have text object with formula: =Sum(ordercnt)/Count(Distinct customer)

In November the result is 1,5. This is correct. (We have 3 orders and two customers).

In December the result is 1. This is correct. (We have 2 orders and two customers).

Without selections result is 2,5. This is correct too. (We have 5 orders and only two customers).
But i need somthing different. I need 1,25 in result expresion. (1,5+1)/2 (Average value of this expression in two months)

Thnx

Need help in calculation

Tue, 22 Dec 2009 01:05:01 GMT

Try this:

=(Sum(ordercnt)/Count(Distinct customer))/Count(DISTINCT month)

Need help in calculation

Tue, 22 Dec 2009 01:05:56 GMT

replace your expression in the text box with

=avg(aggr(Sum(ordercnt)/Count(Distinct customer) , month))

regards

Need help in calculation

Tue, 22 Dec 2009 01:11:44 GMT

I was putting that together right before you posted it; the AGGR function is the best way to solve this.

Need help in calculation

Tue, 22 Dec 2009 01:14:47 GMT

Thanks Jason, I made some progress

Need help in calculation

Wed, 23 Dec 2009 15:17:39 GMT

Thanks!

But i change rules at play)

Lets add one column and one row to Orders table:

Orders:
LOAD * INLINE
[date_c, customer, region, ordercnt
2009-11-1, first, a, 1
2009-11-1, second, b, 1
2009-11-2, first, a, 1
2009-11-2, third, a, 1
2009-12-1, first, a, 1
2009-12-1, second, b, 1
];

And create bar chart with one dimension "Region". And expression "Sum(ordercnt)/Count(Distinct customer)"

Region A in november got 1,5. In december 1
Without selections result is 2, but i need 1,25
With formula =(Sum(ordercnt)/Count(Distinct customer))/Count(DISTINCT month) result is 1
With formula=avg(aggr(Sum(ordercnt)/Count(Distinct customer) , month)) result is 1,17 (?)

Now i am working in 8,5 version

Need help in calculation

Thu, 24 Dec 2009 19:28:23 GMT

i think it's just a problem of definition of your average.

with the first formula :

(6 orders / 3 customer ) / (2 month) = 1 => it's the average number of orders by customer and by month

with the second formula

( ( 4 orders / 3 customers) in november + ( 2 orders / 2 customers) in december) / 2 = 0.5 * ( 4/3 + 1) = 1.17

=> it's the month-average of the number of orders by customer.