7 Replies Latest reply: Oct 7, 2012 4:14 AM by whiteline _ RSS

    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)