Skip to Content
author's profile photo Former Member
Former Member

Max Value issues

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
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Jan 14, 2016 at 03:27 PM

    Hi ,

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

    Thanks.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 14, 2016 at 03:11 PM

    Hi,

    use this.

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

    Amit

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 14, 2016 at 04:26 PM

    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
    10|10000 characters needed characters exceeded

    • Former Member SESHU PS

      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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.