Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
vinay_bangari
Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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

Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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

vinay_bangari
Not applicable

Re: Add expression based on single dimension in multi-dimensional table

do you have licened version or personal edition.

Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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.

vinay_bangari
Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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)

vinay_bangari
Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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

Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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.

vinay_bangari
Not applicable

Re: Add expression based on single dimension in multi-dimensional table

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

Re: Add expression based on single dimension in multi-dimensional table

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.