Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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

Re: Aggregation on two dimensions in expression

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

as aggr formula by default  ignores the chart dimensions

Not applicable

Re: Aggregation on two dimensions in expression

How to do that?

Not applicable

Re: Aggregation on two dimensions in expression

try

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

Not applicable

Re: Aggregation on two dimensions in expression

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

Not applicable

Re: Aggregation on two dimensions in expression

What about something more simple like:

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

MVP & Luminary
MVP & Luminary

Re: Aggregation on two dimensions in expression

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

Re: Aggregation on two dimensions in expression

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

Re: Aggregation on two dimensions in expression

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

Re: Aggregation on two dimensions in expression

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