# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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)

1 Solution

Accepted Solutions
Master II

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
Master II

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

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
Master II

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.

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

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
Author

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.

Master II

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