topic Re: How to calculate Sum of Means ? in QlikView

How to calculate Sum of Means ?

Mon, 07 Jul 2014 06:11:29 GMT

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)

Re: How to calculate Sum of Means ?

hic — Mon, 07 Jul 2014 06:36:38 GMT

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

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

HIC

Re: How to calculate Sum of Means ?

whiteline — Mon, 07 Jul 2014 06:44:05 GMT

Hi.

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

Re: How to calculate Sum of Means ?

alexandros17 — Mon, 07 Jul 2014 06:44:28 GMT

Here it is

Re: How to calculate Sum of Means ?

Anonymous — Mon, 07 Jul 2014 06:47:09 GMT

Hi,

Have a look on RangeAvg() function.

Re: How to calculate Sum of Means ?

er_mohit — Mon, 07 Jul 2014 06:56:52 GMT

See the attached file

Re: How to calculate Sum of Means ?

Mon, 07 Jul 2014 08:27:11 GMT

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:

Re: How to calculate Sum of Means ?

Mon, 07 Jul 2014 08:28:19 GMT

Please check my reply to above post. Thanks.

Re: How to calculate Sum of Means ?

whiteline — Mon, 07 Jul 2014 09:07:41 GMT

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.

Re: How to calculate Sum of Means ?

whiteline — Mon, 07 Jul 2014 09:10:12 GMT

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.

Re: How to calculate Sum of Means ?

Mon, 07 Jul 2014 09:37:41 GMT

Thanks alot, It solved my problem.