Max Value issues

[simonpeart]

Hello everybody,

Just getting a bit confused with the MAX value function in WEBI 4.

Setup

I'm using a BEX that has 2 dimensions and a measure.

2 Dimensions being Account Name and Trading Season Desc

Measure being Ordered Quantity.

What i'm trying to do is have a 2 column table with the left column being the Account Name and the right column displaying the best year of sales they had.

I have a single variable that states =Max([Ordered Quantity]) ForAll([Trading Season - Trading Season Desc (Key)])

The issue that i'm having is that the Max function keeps bringing back the SUM of all the Trading Seasons for each account if I don't have the Trading Season column in, if I add the Trading Season column in, I just get 3 to 4 rows per account detailing which trading season it is and then the Max column will have the correct figure in but be repeated for however many rows there are Trading Seasons per account.

Example data

Account	Trading Season	Header 3
Account 1	2012	300
	2013	200
	2014	500
	2015	600

What I want

Account	Max
Account 1	600

What I get with the Max variable

Account	Max
Account 1	1600

[former_member199945 · Accepted solution]

Hi ,

Create new variable for header 3 with dimension as Qualification . Now apply max function on this variable .

Thanks.

[simonpeart]

Thank you, that's worked fine.  Now, just a quick follow up question.

I've created another variable that takes the Max Quantity and converts it into 3 sizes.

Farmer Size - Max Sold

=Max([Dimension - Ordered Quantity]) ForAll([Farm Size].[Crop Year Description])

Farm Size

=If([Farmer Size - Max Sold] >= 0 And [Farmer Size - Max Sold] <500) Then "Small"

ElseIf([Farmer Size - Max Sold] >= 500 And [Farmer Size - Max Sold] <1200) Then "Medium"

ElseIf([Farmer Size - Max Sold] >1200) Then "Large"

On the table, it displays correctly so that anything between 0 and 500 is small, between 500 and 1200 is medium and over 1200 is large and it gives me a nice selection of small, medium and large labels against each account and each max quantity, most of them being small.  Now the issue seems to be that when I come and do a filter to just filter out either small, medium or large, it only gives me the option of large even through small and medium are on the table as well.

[former_member199945]

Hi,

Which filter you are using try to use input filter bar and use relevant variable in filter bar and see  either it shows all 3 values?

Thanks

[simonpeart]

I get the exact same result with either the filter bar or the Report Filter option.  If I have the Max Sold as a measure and then link the Farm Size to that, I only get large even though the report includes Small, Medium and Large.  If I click to filter out the Large farms, it won't actually remove any of the entries and keeps showing all of the Small, Medium and Large entries.

[amitrathi239]

Hi,

use this.

=[Value] Where ([Trading Season]=Max([Trading Season]) In ([Product]))

Amit