Add expression based on single dimension in multi-dimensional table

[#1]

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:

Product	User	Price	Date
Paper	John Doe	$6.00	January 1, 2012
Paper	Jane Doe	$5.45	May 1, 2012
Paper	John Doe	$7.00	July 1, 2012
Paper	Susan Smith	$4.50	February 1, 2012
Paper	Jane Doe	$5.75	August 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:

Product	User	Average Price Paid	Final Price
Paper	John Doe	$6.50	$5.75
Paper	Jane Doe	$5.60	$5.75
Paper	Susan 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?

[Anonymous]

Is the recent time will be same for each and every product or it will be different.

[#3]

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.

[Anonymous]

Hope the attached example will help you . other wise can you export some sample data from your straight table in the form of excel sheet

so that would be easy for me.

[Anonymous]

Hi Elie,

Try following :-

Script:

table1:

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

];

table2:

load

*,

date(Date#(DATE1,'DD-MM-YYYY'),'DD-MM-YYYY')as FinalDate

Resident table1 Order by DATE1  ;

DROP table table1;

Concatenate

load PRODUCT,

  FirstSortedValue(PRICE,-FinalDate) as FinalPrice

Resident table2 group by PRODUCT ;

Now take straight table and apply following:

Dimension1:- PRODUCT

Dimension2:- USER

Dimension2:-ADD CALCULATED DIMENSION-- =aggr(FinalPrice,PRODUCT)

Expression:- Avg(PRICE)

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.

Thanks,