QlikView - Get Value of top 1 into Text Object

[Anonymous]

Hi guys,

I have list of Codes for a metric called Sales.

In a table I can put the rank of each code, based on Sum(Value).

Now I want to put the Sum(value) of the top1 into a text object.

But in my filters I have to select:

- Month Year;

- Code;

- Metric.

I want to show the top 1 value, ignoring the filter Code.

Giving you a context:

I have the above table, when I select Metric and MonthYear:

My top 1 Sum(value) is Code=1295.

So when I add a selection in Code = 604, I want to show in a text object the value of the top 1, corresponding to the filters I'm applying, except Code.

I've attached a qvw sample. Please see Sheet Rankings.

Thanks in advanced,

Sílvia

[sunny_talwar]

Try this:

='Top 1 value:

' & Sum({<Code = {"=Rank(Sum({<Code>}Value)) < 2"}>}Value)

[vishsaggi · Accepted solution]

Try this?

= Max(Aggr(Sum(Value), Code))

[Anonymous]

Hi Sunny,

For the example I gave your solution works fine.

But in my real app Vishwarath solution works better.

Thanks for your help anyway

Regards,

Sílvia

[Anonymous]

Hi Vishwarath,

Your solution it's what I was looking for.

Thanks for your help.

Regards,

Sílvia

[sunny_talwar]

I am glad it worked, but did it really work (at least in its current form)?

When Code 604 is selected, I am seeing 34030.73 instead of 52071.71... is this what you wanted? or did you change the expression to this

Max({<Code>} Aggr(Sum({<Code>} Value), Code))

Also, I am not sure when was the expression I gave did not work? I would have expected to be slightly better performing because Aggr() slows down performance.

But having said all that, we are glad you got what you were looking for

[Anonymous]

Hi Sunny,

I've changed the expression to this:

Max({<Code, Manager, Consultor>} Aggr($(vVendasRankFixo), Code))

It gives me the right value.

I choosed not to use your expression, because it would cause me to redo a large number of variables.

In this case I only put the fields I want to ignore in the set analysis of max function.

But, do you think there is a better way to do this?

Sílvia

[vishsaggi]

True sunny. I believe to her initial question that says to show value of Top 1 even when you select specific code.

" My top 1 Sum(value) is Code=1295.

So when I add a selection in Code = 604, I want to show in a text object the value of the top 1, corresponding to the filters I'm applying, except Code. "

So if you select 604 the rank for that code is 1 so it is displaying 34030.73 in the text box.

May be Silvia should use your expression if she wants to display the max value irrespective of Code selections. Correct me?

[sunny_talwar]

I don't know what $(vVendasRankFixo) is, but I try to avoid Aggr() function as much as possible. From the Aggr() function I am seeing, I think that set analysis is possible and if it is, I would personally take that route. But, if you are comfortable with the above and don't want to leave your comfort zone then stick to what vishsaggi‌‌‌ has recommended. Many times we look for a better performing solution and don't even know or understand what its doing which is a troubleshooting nightmare.

So, I guess its all upto you to decide what you want to do

[sunny_talwar]

Like I said, if we add set analysis to ignore selection in Code, yours will work as well.... but its just a matter of choice here to use one vs. the other expression.... Look at my response above