7 Replies Latest reply: Feb 23, 2013 7:27 PM by Rob Wunderlich

# how to return a value based on 2 dimensions

hello all

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

#### Month

100

jeepblackjan
150jeepblackfeb
155jeepredjan
100jeepredfeb
100suvbluejan
100suvredjan
175suvbluefeb
100suvredfeb

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

• ###### Re: how to return a value based on 2 dimensions

You can load data doing for example

MyTab:

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

• ###### Re: how to return a value based on 2 dimensions

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

• ###### Re: how to return a value based on 2 dimensions

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

• ###### Re: how to return a value based on 2 dimensions

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

• ###### Re: how to return a value based on 2 dimensions

Hi.

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

• ###### Re: how to return a value based on 2 dimensions

see the attched file

hope this helps

• ###### Re: how to return a value based on 2 dimensions

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

=max(TOTAL <Product, Color> PRICE)

-Rob

http://robwunderlich.com