Search
Search

# 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

##### Narashimman K Sreplied

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