Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
in your aggr formula you need to include the product dimension also
as aggr formula by default ignores the chart dimensions
How to do that?
try
Min({$<Product=,Dealer_ID=>}Aggr(Avg({$<Dealer_ID=>}TOTAL <Dealer_ID> Price),Product,Dealer_ID))
No luck , It gives me 200 for both computer and laptop
What about something more simple like:
aggr(Min({$<Product=,Dealer_ID=>}Price),Product)
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).
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.
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
Thanks nikolaos , It works for me , Can you please ellaborate this how it works...?