Returning the Max values of an existing expression

[#1]

Hello QV Community, I hope you can offer some help.

I have an existing expression that returns a set of values for group of user responses from a survey.

if(Response_Level_2B='Office',(sum ({$<Source_Key={2},Response_Level_2={'Office'}>}TOTAL<User_Profile_ID_SourceDB> Ans_Percent)/sum ({$<Source_Key={2}>}TOTAL<User_Profile_ID_SourceDB> Ans_Percent)/1)*sum ({$<Source_Key={3}>}Ans_Percent)/sum ({$<Source_Key={3}>}TOTAL<User_Profile_ID_SourceDB> Ans_Percent),if(Source_Key=2,sum ({$<Source_Key={2}>}Ans_Percent)/sum ({$<Source_Key={2}>} TOTAL<User_Profile_ID_SourceDB> Ans_Percent),0))

This returns the following information

User_Profile_ID_SourceDB	Response_Level_4	Expression
9652	Room	10%
9652	Out of Office	13%
9652	Open Space	23%
9652	Home	24%
9652	Desk	30%
9692	Open Space	3%
9692	Room	5%
9692	Home	18%
9692	Desk	33%
9692	Out of Office	41%
9713	Room	10%
9713	Open Space	13%
9713	Out of Office	21%
9713	Desk	23%
9713	Home	33%
9763	Home	1%
9763	Open Space	23%
9763	Room	23%
9763	Desk	53%
9764	Home	12%
9764	Open Space	17%
9764	Room	19%
9764	Out of Office	22%
9764	Desk	31%
9765	Out of Office	2%
9765	Home	2%
9765	Open Space	5%
9765	Room	5%
9765	Desk	86%

I would like to be able to wrap a function around this expression that will return the highest response for each of the user responses so I will have the following information.

User_Profile_ID_SourceDB	Response_Level_4	Expression
9652	Desk	30%
9692	Out of Office	41%
9713	Home	33%
9763	Desk	53%
9764	Desk	31%
9765	Desk	86%

Thanks

Rob

[Gysbert_Wassenaar]

Try using the Dimension Limits option. Open the properties window of your chart and go to the Dimension Limits tab. If that tab doesn't exist change the chart type to Straight Table. On the Dimension Limits tab click on the Response_Level_4 dimension. Then select Show only Largest 1 values. Also make sure the Show Others option is disabled.

See the below image for an example. The left table shows the sum of sales for every Product-SalesPerson combination, the table on the right only the top values per product:

[#3]

Hi, yes I tried that which works for a similar table I have but for this specific expression it doesn't return the result.  That's why I'm looking for the wrap around.

[#4 · Accepted solution]

Hello, I received an answer to this question and wanted to make sure that the right person got the credit.

So, thank you Daniel Rozental - with a minor adjustment this worked for the problem above.

if( yourexpression = max(total <User_Profile_ID_SourceDB, Ans_Percent>

aggr( yourexpression, User_Profile_ID_SourceDB, Response_Level_4)),

yourexpression)

Cheers

Rob