How to calculate Sum of Means ?

[#1]

Help me write some expressions, attached is the sample document

My first chart i.e. "Avg Mean Price Per Product" would be like

Sum(Mean price of all purchases of a customer)/Total Number of Customers

My first chart i.e. "Avg Mean Price Per Product" would be like

Sum(Max price of all purchases of a customer)/Total Number of Customers

***     There can be multiple purchases for a product against single customer. I have updated the document too.

Any help is highly appreciated.

Message was edited by: Mubbasher Khaliq (Multiple purchases for a product against single customer added)

[hic]

If you want to average per customer, then sum the averages, you should use

     Sum(Aggr(Avg(Price),Customer))

HIC

[whiteline]

Hi.

aggr() function can help you to calculate the expression for the aggregation level that differs from the chart dimensions.

[alexandros17]

Here it is

[Anonymous]

Hi,

Have a look on RangeAvg() function.

[er_mohit]

See the attached file

[#7]

Thanks, This helped me a lot , but when I write this expression

If within Aggr function I use Sum/Count rather than Avg then the out put of Count is not right. As can be seen below.

Expression:

Sum(Aggr(Count(1),Product))

Output:

Why it is counting every product count against single customer.

Desired Output:

[#8]

Please check my reply to above post. Thanks.

[whiteline · Accepted solution]

You should add Customer as dimension to aggr, otherwise you choose to calculate aggr() regardless of customer (aka total<> modifier).

Read the help about aggr() function.

[whiteline]

Since QV can't choose the customer to show the result it takes the first one.

To make the aggr() working as total modifier you should add 'nodistinct', then QV repeats 25 for each customer.