Problem getting min value
hi, the problem is the following:
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.
Thank you for any suggestion
Narashimman K S replied
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.
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
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