Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Average based on another dimension

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:

InvoiceProductCostProfit
Invoice AItem A$10$15
Invoice AItem B$20$40
Invoice BItem C$15$15
Invoice BItem D$20$40
Invoice CItem E$5$25
Invoice DItem 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)

Tags (1)
1 Solution

Accepted Solutions
whiteline
Honored Contributor II

Re: Average based on another dimension

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))

7 Replies
whiteline
Honored Contributor II

Re: Average based on another dimension

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

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)


talk is cheap, supply exceeds demand
whiteline
Honored Contributor II

Re: Average based on another dimension

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

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?


talk is cheap, supply exceeds demand
MVP
MVP

Re: Average based on another dimension

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.

Not applicable

Re: Average based on another dimension

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.

whiteline
Honored Contributor II

Re: Average based on another dimension

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.

Community Browser