Okay, so Total<Product.name> will filter based on the first dimension. That helps a lot!
But rather than the average, I want to get the closing price. So I'm looking for something along the following (I just can't find the syntax):
price from Total<Product> order by Sale.date limit 1
That is - return the price for the most recent sale of the product.
Original data is in the form of a few tables - customer, product, sale. Each time a product is sold, an entry is added to the sale table for that product + customer + sale date. That entry has an additional field of the actual price paid.
The closing price is the most recent price paid by any customer. That is, if the data in the sale table was grouped by product alone, it would be the price corresponding to the entry with the most recent date. In the example above (in the original question), that would put the closing price for the stated product at $5.75, since that was the most recent price paid.
Each sale has it's own final price. So if the sale table was filtered by product (much the same way you did average(Total<Product.id> Sale.price) to get the average price paid) then the number I'm looking for is the price for the row with the most recent date.
Since the product will appear in my output multiple times (once for each customer), each row for a given product will have the same value in this field.
Try following :-
LOAD * INLINE [
PRODUCT, USER, PRICE, DATE1
Paper, John Doe, $6.00, 01-01-2012
Paper, Jane Doe, $5.45,01-05-2012
Paper, John Doe, $7.00, 01-07-2012
Paper, Susan Smith, $4.50, 01-02-2012
Paper, Jane Doe, $5.75, 01-08-2012
Resident table1 Order by DATE1 ;
DROP table table1;
FirstSortedValue(PRICE,-FinalDate) as FinalPrice
Resident table2 group by PRODUCT ;
Now take straight table and apply following:
Dimension2:-ADD CALCULATED DIMENSION-- =aggr(FinalPrice,PRODUCT)
Above manipulation will give you following answer.
PRODUCT USER =aggr(FinalPrice,PRODUCT) Avg(PRICE) Paper John Doe $5.75 6.5 Paper Jane Doe $5.75 5.6 Paper Susan Smith $5.75 4.5
Please let me know if its working.