Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Is the recent time will be same for each and every product or it will be different.
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.
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.
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,