Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregation on two dimensions in expression

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_IDProductPrice
1Computer200
1Computer300
3Computer400
4Computer500
5Computer600
1Laptop300
2Laptop400
3Laptop500
4Laptop600
5Laptop700

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.

9 Replies
lironbaram
Partner - Master III
Partner - Master III

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

as aggr formula by default  ignores the chart dimensions

Not applicable
Author

How to do that?

Not applicable
Author

try

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

Not applicable
Author

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

Not applicable
Author

What about something more simple like:

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

Gysbert_Wassenaar

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).


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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