How to Get a Dynamic Label for a Calculated KPI

[crichter14]

Hello there,

Though a lot of reading I've gotten my KPI box to work...yay!  However,  I am wondering how to make the related label dynamic? 

Example, I know that the Max QTY is 10, but in the KPI I want to show the label related to that KPI (i.e. Jan) in addition to the QTY.

How can I make that work?  Sorry if it's a basic question - I'm really struggling on this one.  I stole this table from another thread, but I don't want the quantity - I want the month.

MONTH	PRODUCT	QTY
Jan	12345678	10
Feb	12345678	-5
Mar	12345678	-10
April	12345678	5

A simplified example of my code.  I want the "CATEGORY" field related to the Maximum field value.

MAX(AGGR(Sum({$<
[SET_ANALYSIS_1]={"Example"}>}
SALES_AMOUNT)
/
Sum({$<
[SET_ANALYSIS_2]={"Example"}>}
SALES_AMOUNT_2)
,CATEGORY))

Ideas?

[raji6763 · Accepted solution]

hi,

you can achieve using FirstSortedValue function.

FirstSortedValue(MONTH,-Aggr(Max(QTY),MONTH))

you can modify according to your example

regards,

raji

[crichter14]

Thank you Raj,

This works for my question.  

Then I had to amend the code to get the smallest value as well (second request from user).  Removing the minus sign in front of AGGR to gives that.  I had to use the RANK function to eliminate the nulls, but it works.  Thank you Raj!