how to return a value based on 2 dimensions

[Anonymous]

hello all

not sure what the best way or even how to do this.

PRICE	Product	Color	Month
100	jeep	black	jan
150	jeep	black	feb
155	jeep	red	jan
100	jeep	red	feb
100	suv	blue	jan
100	suv	red	jan
175	suv	blue	feb
100	suv	red	feb

how do i get the max price based on 2 dimensions above..  product and color? regardless of month, even though i have more than 2 years worth of data..

i tried max and aggr but i keep getting calc errors.

not sure whats best or how to go about it.

thanks

[alexandros17]

You can load data doing for example

MyTab:

Load

     product,

     color,

     max(price) as maximum

group by product, color;

if you put do:

Main table

left join

MyTab

you will have a new column with max value ...

Hope it helps

[Anonymous]

that would work but forgot to mention i can mess with the load as im just tacking on this some extra data to the app that already exist.

was wondering if there was a way to do it as an expression or a calculated dimension on the chart im using

[alexandros17]

I've not understood your goal :

you have a straight table with 2 dimensions (product and color) and you want to computee the max price?

If this, how do you want to show it ? The same value in each row? Only the row with max?

let me know

[Anonymous]

its actually a pivot table (not sure if that makes a difference)

if the picture below.. i have the product (style) and the color.

but this style has 2 prices due to month data.. but since i cant use month as a 3rd criteria (its not known) i need to pull the max price.

in this calculated dimension or change it to an expression...

i tried max and agg but im thinking im still missing sometihng.

thanks

[whiteline]

Hi.

Could you also post the resulting table that you're trying to achive (according to the table posted above) ?

[SunilChauhan]

see the attched file

hope this helps

[rwunderlich]

You can do it with aggr(), but it's easier with TOTAL

=max(TOTAL <Product, Color> PRICE)

-Rob

http://robwunderlich.com