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 600What I want
Account Max Account 1 600What I get with the Max variable
Account Max Account 1 1600Hi ,
Create new variable for header 3 with dimension as Qualification . Now apply max function on this variable .
Thanks.
Hi,
use this.
=[Value] Where ([Trading Season]=Max([Trading Season]) In ([Product]))
Amit
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.
Add a comment