Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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))
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)
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?
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.
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.
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.