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?
see the sample application and sample data . Hope this helps you .
I can't open the file - issue with conflicting Personal Editions.
do you have licened version or personal edition.
It appears I'm using Personal Edition (it's for work - I assumed it was Enterprise edition, as there were licensing issues, but now it appears otherwise).
I'm downloading the Personal Edition right now that's free on my local machine, so I'll be able to view it here momentarily.
if you use a personel edition what you do is using the excel sheet load the data into one application. And then take a straight table , in that give product and users as dimensions and in the two expressions
1. Avg(Price0
2.Avg( total<Product> Price)
if you use a personal edition you cannot be able see an application created by another person or another developer.
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.
can you define what is a closing price. I did not understood right. or else post a sample application with sample data so that i can help you with.
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.