topic Re: how to return a value based on 2 dimensions in QlikView https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464955#M562915 <HTML><HEAD></HEAD><BODY><P>see the attched file </P><P></P><P></P><P>hope this helps</P></BODY></HTML> Sat, 23 Feb 2013 11:02:42 GMT SunilChauhan 2013-02-23T11:02:42Z how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464949#M562909 <HTML><HEAD></HEAD><BODY><P>hello all</P><P></P><P>not sure what the best way or even how to do this.</P><P></P><P></P><TABLE border="1" cellpadding="3" cellspacing="0" class="jiveBorder" style="width: 100%; border: 1px solid #000000;"><TBODY><TR><TH align="center" style="background-color: #6690bc;" valign="middle"><H5><SPAN style="color: #ffffff;"><STRONG>PRICE<BR /></STRONG></SPAN></H5></TH><TH align="center" style="background-color: #6690bc;" valign="middle"><H4>Product</H4></TH><TH align="center" style="background-color: #6690bc;" valign="middle"><H4>Color</H4></TH><TH align="center" style="background-color: #6690bc;" valign="middle"><H4>Month</H4></TH></TR><TR><TD><P>100</P></TD><TD>jeep</TD><TD>black</TD><TD>jan</TD></TR><TR><TD>150</TD><TD>jeep</TD><TD>black</TD><TD>feb</TD></TR><TR><TD>155</TD><TD>jeep</TD><TD>red</TD><TD>jan</TD></TR><TR><TD>100</TD><TD>jeep</TD><TD>red</TD><TD>feb</TD></TR><TR><TD>100</TD><TD>suv</TD><TD>blue</TD><TD>jan</TD></TR><TR><TD>100</TD><TD>suv</TD><TD>red</TD><TD>jan</TD></TR><TR><TD>175</TD><TD>suv</TD><TD>blue</TD><TD>feb</TD></TR><TR><TD>100</TD><TD>suv</TD><TD>red</TD><TD>feb</TD></TR></TBODY></TABLE><P></P><P></P><P>how do i get the max price based on 2 dimensions above..&nbsp; product and color? regardless of month, even though i have more than 2 years worth of data..</P><P></P><P>i tried max and aggr but i keep getting calc errors.</P><P></P><P>not sure whats best or how to go about it.</P><P></P><P>thanks</P></BODY></HTML> Fri, 22 Feb 2013 20:41:49 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464949#M562909 Anonymous 2013-02-22T20:41:49Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464950#M562910 <HTML><HEAD></HEAD><BODY><P>You can load data doing for example</P><P></P><P>MyTab:</P><P>Load</P><P>&nbsp;&nbsp;&nbsp;&nbsp; product,</P><P>&nbsp;&nbsp;&nbsp;&nbsp; color,</P><P>&nbsp;&nbsp;&nbsp;&nbsp; max(price) as maximum</P><P>group by product, color;</P><P></P><P>if you put do:</P><P></P><P>Main table</P><P></P><P>left join</P><P></P><P>MyTab</P><P></P><P>you will have a new column with max value ...</P><P></P><P>Hope it helps</P></BODY></HTML> Fri, 22 Feb 2013 20:59:45 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464950#M562910 alexandros17 2013-02-22T20:59:45Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464951#M562911 <HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>was wondering if there was a way to do it as an expression or a calculated dimension on the chart im using</P></BODY></HTML> Fri, 22 Feb 2013 21:04:18 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464951#M562911 Anonymous 2013-02-22T21:04:18Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464952#M562912 <HTML><HEAD></HEAD><BODY><P>I've not understood your goal :</P><P>you have a straight table with 2 dimensions (product and color) and you want to computee the max price?</P><P>If this, how do you want to show it ? The same value in each row? Only the row with max?</P><P></P><P>let me know</P></BODY></HTML> Fri, 22 Feb 2013 21:10:53 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464952#M562912 alexandros17 2013-02-22T21:10:53Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464953#M562913 <HTML><HEAD></HEAD><BODY><P>its actually a pivot table (not sure if that makes a difference)</P><P></P><P><IMG class="jiveImage" src="https://community.qlik.com/" style="max-width: 1200px; max-height: 900px;" /></P><P></P><P>if the picture below.. i have the product (style) and the color.</P><P></P><P>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.</P><P></P><P>in this calculated dimension or change it to an expression...</P><P></P><P>i tried max and agg but im thinking im still missing sometihng.</P><P></P><P>thanks</P></BODY></HTML> Fri, 22 Feb 2013 21:37:24 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464953#M562913 Anonymous 2013-02-22T21:37:24Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464954#M562914 <HTML><HEAD></HEAD><BODY><P>Hi.</P><P>Could you also post the resulting table that you're trying to achive (according to the table posted above) ?</P></BODY></HTML> Sat, 23 Feb 2013 10:31:13 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464954#M562914 whiteline 2013-02-23T10:31:13Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464955#M562915 <HTML><HEAD></HEAD><BODY><P>see the attched file </P><P></P><P></P><P>hope this helps</P></BODY></HTML> Sat, 23 Feb 2013 11:02:42 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464955#M562915 SunilChauhan 2013-02-23T11:02:42Z Re: how to return a value based on 2 dimensions https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464956#M562916 <HTML><HEAD></HEAD><BODY><P>You can do it with aggr(), but it's easier with TOTAL</P><P></P><P>=max(TOTAL &lt;Product, Color&gt; PRICE)</P><P></P><P>-Rob</P><P><A class="jive-link-external-small" href="http://robwunderlich.com">http://robwunderlich.com</A></P></BODY></HTML> Sun, 24 Feb 2013 00:27:15 GMT https://community.qlik.com/t5/QlikView/how-to-return-a-value-based-on-2-dimensions/m-p/464956#M562916 rwunderlich 2013-02-24T00:27:15Z