topic Re: Minimum with Ranking in QlikView

Minimum with Ranking

rcandeo — Thu, 29 Sep 2016 14:53:19 GMT

In this example, I neeed to find the minimum value considering ony the 10 bigger values.

So where it shows 990, I need to be 1100 as this is the 10th bigger value

My actual expression is:

=Min(ALL Aggr(Sum({<Name=>} Value),Name))

Thank You

Re: Minimum with Ranking

sunny_talwar — Thu, 29 Sep 2016 14:58:08 GMT

May be this:

=Min(ALL Aggr(Sum({<Name = {"=Rank(Sum(Value)) < 11"}>} Value),Name))

Re: Minimum with Ranking

sunny_talwar — Thu, 29 Sep 2016 14:59:29 GMT

Why are you using ALL? This is something which will be discontinued in future version. If this is something you use in a text box, you might just need this

=Min({1} Aggr(Sum({<Name = {"=Rank(Sum(Value)) < 11"}>} Value),Name))

or this in chart

=Min({1} TOTAL Aggr(Sum({<Name = {"=Rank(Sum(Value)) < 11"}>} Value),Name))

Re: Minimum with Ranking

rcandeo — Thu, 29 Sep 2016 18:18:54 GMT

Unfortunatelly none of these options worked as expected.

I need to have the option to select one of this names and the results cannot change, but they are being changed.

See qvw attached please?

Re: Minimum with Ranking

sunny_talwar — Thu, 29 Sep 2016 18:21:14 GMT

This?

=Min(ALL Aggr(Sum({<Name = {"=Rank(Sum({1}Value)) < 11"}>} Value),Name))

Re: Minimum with Ranking

rcandeo — Fri, 30 Sep 2016 12:28:48 GMT

almost there. I am sending qvw again with another field - Category. My intention is that min value continue being the same, no matter if I select category A or B. Can you help me with this?

Re: Minimum with Ranking

sunny_talwar — Fri, 30 Sep 2016 12:31:58 GMT

Try this:

=Min(ALL Aggr(Sum({<Name = {"=Rank(Sum({$<Category=>}Value)) < 11"}, Category>} Value),Name))

Re: Minimum with Ranking

rcandeo — Fri, 30 Sep 2016 13:28:38 GMT

Perfect. Just to complete the sentence to be independent from selections os Name and Category:

=Min(ALL Aggr(Sum({<Name = {"=Rank(Sum({$<Name,Category=>}Value)) < 11"},Category>} Value),Name))