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

Add expression based on single dimension in multi-dimensional table

Mon, 24 Sep 2012 18:25:37 GMT

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?

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

Anonymous — Mon, 24 Sep 2012 20:44:50 GMT

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

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

Mon, 24 Sep 2012 20:53:39 GMT

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

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

Anonymous — Mon, 24 Sep 2012 20:57:41 GMT

do you have licened version or personal edition.

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

Mon, 24 Sep 2012 21:00:10 GMT

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.

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

Anonymous — Mon, 24 Sep 2012 21:01:08 GMT

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)

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

Anonymous — Mon, 24 Sep 2012 21:02:16 GMT

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

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

Mon, 24 Sep 2012 21:14:48 GMT

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.

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

Anonymous — Tue, 25 Sep 2012 12:36:34 GMT

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.

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

Tue, 25 Sep 2012 14:52:22 GMT

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.

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

Anonymous — Tue, 25 Sep 2012 15:20:28 GMT

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

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

Tue, 25 Sep 2012 15:28:39 GMT

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.

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

Anonymous — Tue, 25 Sep 2012 15:39:52 GMT

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.

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

Anonymous — Mon, 17 Oct 2016 12:09:42 GMT

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,