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:


      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:



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


      and the computed field is simply




      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?