13 Replies Latest reply: Oct 17, 2016 8:09 AM by Pooja Singh RSS

    Add expression based on single dimension in multi-dimensional table

      I have a table with 2 dimensions - product and user. For each line in the table, I want to show the average price the user paid for that product, and the latest recorded price for that product. Each sale of a product is a record in a table with the price it was sold at, and the date on which it was sold.

       

      For example, I have the following recorded sales:

       

      ProductUserPriceDate
      PaperJohn Doe$6.00January 1, 2012
      PaperJane Doe$5.45May 1, 2012
      PaperJohn Doe$7.00July 1, 2012
      PaperSusan Smith$4.50February 1, 2012
      PaperJane Doe$5.75August 1, 2012

       

      The two dimensions are computed as follows:

       

      Product.name

      Aggr(if(Count(Purchase.amount) > 0, User.username), User.username)

       

      and the computed field is simply

       

      Average(Purchase.amount)

       

      I want the output to be something like the following:

       

      ProductUserAverage Price PaidFinal Price
      PaperJohn Doe$6.50$5.75
      PaperJane Doe$5.60$5.75
      PaperSusan Smith$4.50$5.75

       

      I can calculate the average easily enough, but how do I determine what the final price was, if I want to only filter by the first dimension (product) and not by the second dimension (user) so that all users will have the same final price for a given product?