topic Re: Aggregation on two dimensions in expression in QlikView

Aggregation on two dimensions in expression

Thu, 25 Jul 2013 11:06:46 GMT

Hi ,

I have added one dimension to chart Products(Computers , Laptops)

I have to calculate best price of any dealer within each product

Sample data is

Dealer_ID	Product	Price
1	Computer	200
1	Computer	300
3	Computer	400
4	Computer	500
5	Computer	600
1	Laptop	300
2	Laptop	400
3	Laptop	500
4	Laptop	600
5	Laptop	700

I have added Product as dimension on chart so my formula is

Min({$<Product=,Dealer_ID=>}

Aggr(

Avg({$<Product=,Dealer_ID=>}TOTAL <Dealer_ID> Price)

,Dealer_ID)

)

So my expected result is 200 for computer and 300 for Laptop

But it gives me 200(which is overall best price disregarding product dimension which I have added in chart) for Computer and 0 for Laptop

Any help is really appreciated.

Re: Aggregation on two dimensions in expression

lironbaram — Thu, 25 Jul 2013 11:10:26 GMT

in your aggr formula you need to include the product dimension also

as aggr formula by default ignores the chart dimensions

Re: Aggregation on two dimensions in expression

Thu, 25 Jul 2013 11:11:43 GMT

How to do that?

Re: Aggregation on two dimensions in expression

Thu, 25 Jul 2013 11:13:16 GMT

try

Min({$<Product=,Dealer_ID=>}Aggr(Avg({$<Dealer_ID=>}TOTAL <Dealer_ID> Price),Product,Dealer_ID))

Re: Aggregation on two dimensions in expression

Thu, 25 Jul 2013 11:50:59 GMT

No luck , It gives me 200 for both computer and laptop

Re: Aggregation on two dimensions in expression

Thu, 25 Jul 2013 12:19:56 GMT

What about something more simple like:

aggr(Min({$<Product=,Dealer_ID=>}Price),Product)

Re: Aggregation on two dimensions in expression

Gysbert_Wassenaar — Thu, 25 Jul 2013 13:05:50 GMT

If you use Product as dimension and you want the minimum price per product simply use min(Price) as expression. If you want to disregard selections in the Dealer_ID field change it to min({<Dealer_ID=>}Price).

Re: Aggregation on two dimensions in expression

Thu, 25 Jul 2013 13:35:29 GMT

Well I cannot use simple min because one dealer can have multiple prices so I have to find avg price per dealer per product then from these averages I have to get minimum for each product.

Re: Aggregation on two dimensions in expression

Thu, 25 Jul 2013 14:02:28 GMT

If thats the casethe expression you need is:

Min({$<Product=,Dealer_ID=>}Aggr(Avg(Price),Dealer_ID,Product))

Which I guess gives 250 for Computer and 300 for laptop as results

Re: Aggregation on two dimensions in expression

Fri, 26 Jul 2013 06:09:29 GMT

Thanks nikolaos , It works for me , Can you please ellaborate this how it works...?