topic Re: Set Analysis? Find Formula Value for Dimension Value in QlikView

Set Analysis? Find Formula Value for Dimension Value

Anonymous — Wed, 21 Feb 2018 15:28:58 GMT

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.

Re: Set Analysis? Find Formula Value for Dimension Value

sunny_talwar — Wed, 21 Feb 2018 17:36:59 GMT

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.

Re: Set Analysis? Find Formula Value for Dimension Value

Anonymous — Thu, 22 Feb 2018 06:49:56 GMT

Thank you very much, that did the job!

- QS

Re: Set Analysis? Find Formula Value for Dimension Value

kakani87 — Thu, 22 Feb 2018 07:01:47 GMT

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

Re: Set Analysis? Find Formula Value for Dimension Value

fabdulazeez — Thu, 22 Feb 2018 07:16:22 GMT

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

Re: Set Analysis? Find Formula Value for Dimension Value

kakani87 — Thu, 22 Feb 2018 07:55:35 GMT

Thank you Fawaz for the clarification.