Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Average based on another dimension

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2012-10-05
05:12 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

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

9,105 Views

1 Solution

Accepted Solutions

whiteline

Master II

2012-10-06
05:20 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Master II

2012-10-06
05:20 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2012-10-06
05:29 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

4,210 Views

whiteline

Master II

2012-10-06
06:32 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

4,210 Views

2012-10-06
06:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

4,210 Views

swuehl

MVP

2012-10-06
08:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

4,210 Views

Not applicable

2012-10-06
09:25 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

4,210 Views

whiteline

Master II

2012-10-07
04:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.