Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

ProductUserPriceDate
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:

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:

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?

13 Replies
Anonymous
Not applicable
Author

see the sample application and sample data . Hope this helps you .

Not applicable
Author

I can't open the file - issue with conflicting Personal Editions.

Anonymous
Not applicable
Author

do you have licened version or personal edition.

Not applicable
Author

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.

Anonymous
Not applicable
Author

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)

Anonymous
Not applicable
Author

if you use a personal edition you cannot be able see an application created by another person or another developer.

Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Not applicable
Author

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.