6 Replies Latest reply: Dec 24, 2009 2:28 PM by Jean-Jacques Jesua

# Need help in calculation

Hello all!

We have two tables (see attached qvw):

`Dates:LOAD * INLINE[date_c, month2009-11-1, 112009-11-1, 112009-11-2, 112009-12-1, 122009-12-1, 12];Orders:LOAD * INLINE[date_c, customer, ordercnt2009-11-1, first, 12009-11-1, second, 12009-11-2, first, 12009-12-1, first, 12009-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

Try this:

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

• ###### Need help in calculation

hi

replace your expression in the text box with

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

regards

jj

• ###### Need help in calculation

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

• ###### Need help in calculation

Thanks Jason, I made some progress

• ###### Need help in calculation

Thanks!

But i change rules at play)

Lets add one column and one row to Orders table:

`Orders:LOAD * INLINE[date_c, customer, region, ordercnt2009-11-1, first, a, 12009-11-1, second, b, 12009-11-2, first, a, 12009-11-2, third, a, 12009-12-1, first, a, 12009-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

hi

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.

jj