Set Analysis? Find Formula Value for Dimension Value

[Anonymous]

Dear all,

I have the following graph with test data:

Now I want to show a text box like this:

This one was created using the following formula:

'The Dim1 value with the highest value in sum(Expression1) is: ' max(aggr(sum(Expression1), Dim1)) // here I want to see "C", not the formula value.

However, I do not want to show the formula value of 134707, but the dimension value "C". How can I do this? With set analysis?

Please find attached the QVW. Thank you very much in advance.

QS

[sunny_talwar · Accepted solution]

Try this

='The Dim1 value with the highest value in sum(Expression1) is: ' & FirstSortedValue(Dim1, -aggr(sum(Expression1), Dim1)) // here I want to see "C", not the formula value.

[Anonymous]

Thank you very much, that did the job!

- QS

[kakani87]

Hi Sunny .... what  exactly -Aggr() dose here could you explain this expression

[fabdulazeez]

aggr is creating a virtual table with 2 columns dim1 and its sum. FirstSortedValue will get the max(beacuse of negative sign) in DIM1 from that table

[kakani87]

Thank you Fawaz for the clarification.