Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

as aggr formula by default  ignores the chart dimensions

Highlighted
Not applicable

How to do that?

Highlighted
Not applicable

try

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

Highlighted
Not applicable

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

Highlighted
Not applicable

What about something more simple like:

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

Highlighted
MVP & Luminary
MVP & Luminary

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

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.

Highlighted
Not applicable

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

Highlighted
Not applicable

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