Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Problem getting min value

hi, the problem is the following:

(example)

Indicator1

3

9

5

21

11

I have an indicator that has some values (see the example). Now i want another indicator that for each row calculates the max value, over Indicator1, minor of actual value of indicator1. Se the desired result below.

Indicator1    Indicator2

3                   

9                    5

5                    3

21                    11

11                    9

Thank you for any suggestion

Former Member
replied

Interesting!

Without having to bother about the sort order you can achieve using the following methods. Choose the one that will fit your requirement. Find Attached Screenshots, Formula & SQL .

Method 1: Using Relative value. Use this in case if you want to compare current year with previous year for example.

Previous Year Indicator: =RelativeValue([Query 1].[Indicator1];([Query 1].[Year T]);-1)

Growth: =[Query 1].[Indicator1]-[PreviousYear Indicator]

Method 2: Using Derived Tables. In your universe create a derived table that will do the trick for you. Simple and straight forward.

SELECT

  Table5_Carnara.YearT,

  Table5_Carnara.Indicator1,

  Max((Select MAX(Indicator1) FROM Table5_Carnara WHERE Indicator1<(SELECT Indicator1 FROM Table5_Carnara where Table5_Carnara.YearT=Alias_of_Table5_Carnara.YearT))) as NextSmall

FROM

  Table5_Carnara  Alias_of_Table5_Carnara INNER JOIN Table5_Carnara ON (Table5_Carnara.YearT=Alias_of_Table5_Carnara.YearT)

  Group by Table5_Carnara.YearT,   Table5_Carnara.Indicator1

Method 3: If you plan to use Excel for you analysis, you can just rank the indicators and what you need is current Rank -1 (use Vlookup or match functions to get what you want).

Ranking: =Rank([Query 1].[Indicator1])

Method 4: You can setup a small ETL task and add the column in the actual table itself and that will save lot of your time and also, will be faster

Regards,

Naras

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question