topic Re: Average based on another dimension in QlikView

Average based on another dimension

Fri, 05 Oct 2012 21:12:05 GMT

I have a Straight Table for which I'm trying to add a calculated field that is based on another dimension, one which is not part of the straight table.

Essentially, the dimension on the table is customer name. I want to add a column which shows, for each customer, the average profit margin per sale, where profit margin per sale is the total profit / total cost.

To illustrate, below is the data for a single customer:

Invoice	Product	Cost	Profit
Invoice A	Item A	$10	$15
Invoice A	Item B	$20	$40
Invoice B	Item C	$15	$15
Invoice B	Item D	$20	$40
Invoice C	Item E	$5	$25
Invoice D	Item F	$10	$10

When we calculate over the dimension of invoice, I get that Invoice A had a profit margin of 1.83 (55/30), Invoice B was 1.57 (55/35), Invoice C was 5.0 (25/5) and Invoice D was 1.0 (10/10).

The average of these margins, for this customer, is 2.35, which is the number I'm trying to get in my other table. If we just add the costs and the profits, and calculate the margin, we come up with 1.81.

The equation I tried writing was as follows (on a straight table with dimension customer), but it doesn't quite work:

Avg(total<Invoice> aggr(sum(profit) / sum(cost), Customer))

Note, I currently only have access to Personal Edition, and so cannot open other Qlikview files)

Re: Average based on another dimension

whiteline — Sat, 06 Oct 2012 09:20:47 GMT

Hi.

Having a Customer as dimension your expresison is already calculated for each customer.

Then you want to calculate the average of margins of invoices for the customer:

=Avg(aggr(sum(profit) / sum(cost), Invoice))

Re: Average based on another dimension

Gysbert_Wassenaar — Sat, 06 Oct 2012 09:29:08 GMT

Looks like even the avg isn't necessary. This seems to work too:

=aggr(sum(Profit) / sum(Cost), Invoice)

=aggr(sum(Profit) / sum(Cost), Customer)

Re: Average based on another dimension

whiteline — Sat, 06 Oct 2012 10:32:11 GMT

Hi, gwassenaar.

=aggr(sum(Profit) / sum(Cost), Invoice)

This gives a list of values of average margins for each Invoice, and if is used with Customer as dimension, you need avg() to aggregate. If is used with Invoice as dimension it 'works' but the aggr() is useless.

=aggr(sum(Profit) / sum(Cost), Customer)

This gives a list of values of weighted average margins for each Customer, whereas Elie asked about simple average of margins of invoices for each customer.

Re: Average based on another dimension

Gysbert_Wassenaar — Sat, 06 Oct 2012 10:49:37 GMT

In my example I get the same values from all three variants. Obviously I don't get it. Can you explain what I'm doing wrong?

Re: Average based on another dimension

swuehl — Sat, 06 Oct 2012 12:09:20 GMT

gwassenaar,

the task as defined by Elie is to calculate the numbers in the context of the only chart dimension Customer, where you are using additional dimensions Invoice and Product, right?

Remove these two dimensions, and you will see the difference.

Re: Average based on another dimension

Sun, 07 Oct 2012 01:25:20 GMT

OK, just tried this now, and definitely have made progress, but it still doesn't seem correct - at least, the numbers appear to be off.

I tried the following equations, with questionable results:

Avg(aggr(Sum(profit) / Sum(cost), Invoice)

I get a few results saying 0 and a bunch of rows indicating that it's not calculatable.

I tried the following, and it seems to work much better, but I'm not sure why:

Avg(aggr(Sum(profit) / Sum(cost), Invoice, Customer)

The results appear to be much more realistic, and it seems that I'm getting normal results for all customers.

Re: Average based on another dimension

whiteline — Sun, 07 Oct 2012 08:14:49 GMT

Hi.

The expression:

=Avg(aggr(Sum(profit) / Sum(cost), Invoice, Customer)

is also right.

It seems some Invoices has equal names for different customers in your case

(so with my expression it appears only for one customer - my fault).

The above expression resolves this issue.