topic Re: Top and Bottom 3 Tricky in QlikView

Top and Bottom 3 Tricky

Wed, 25 Nov 2020 16:16:04 GMT

Dear All

I have one table which has Value and Amount against Code.

I have use Condition if Amount>=20 than show my Top 3 and Bottom 3.

I have used Dimension Limits, Aggr in Calculated Dimension and Some Expressions but no luck.

Kindly, help me.

Regards

Cire

Re: Top and Bottom 3 Tricky

Gysbert_Wassenaar — Thu, 22 Sep 2016 16:25:39 GMT

Try =If(Aggr(Rank(Sum(Amount)),Code)<=3 or Aggr(Rank(-Sum(Amount)),Code)<=3,Code,'Others')

Re: Top and Bottom 3 Tricky

Thu, 22 Sep 2016 17:26:03 GMT

‌Thanks for the Reply.

BUt as I mentioned it is not straight forward and it is little tricky.

I Have to pick only cases where Amount is greater than 20.

Re: Top and Bottom 3 Tricky

sunny_talwar — Thu, 22 Sep 2016 18:14:45 GMT

Can you share what you desired output needs to look like? I know you have pointed out in your script the desired output, but I am guessing its not complete, because why else would you be here, right? What is missing? Can you elaborate a little?

Re: Top and Bottom 3 Tricky

Thu, 22 Sep 2016 19:30:36 GMT

Hi Sir

I want this Output:

Re: Top and Bottom 3 Tricky

sunny_talwar — Thu, 22 Sep 2016 20:11:52 GMT

I got this

Expression used are the same

Sum({<Code = {"=Sum(Amount) > 20"}>}Value)

Chart on the left uses Largest on dimension limits and chart on the right uses smalles

Re: Top and Bottom 3 Tricky

Fri, 23 Sep 2016 03:29:44 GMT

‌

‌Hi Sir

The Result is coming wrong for Top3 Charts.

Also, I want the Result Amount wise.

As my expressions are very complex so

I Can't use this approach here

Can I get the desired output using calculated dimension

someghing like this:

if(Aggr(Rank(sum({<Code={"=Sum(Amount)>20"}>}Amount)),Code<=3,Code,'others')

Can I use this in calculated dimension.

Re: Top and Bottom 3 Tricky

Gysbert_Wassenaar — Fri, 23 Sep 2016 05:22:46 GMT

=If(Aggr(Rank(Sum({<Code={"=sum(Amount)>20"}>}Amount)),Code)<=3 or Aggr(Rank(-Sum({<Code={"=sum(Amount)>20"}>}Amount)),Code)<=3,Code,'Others')

Re: Top and Bottom 3 Tricky

Fri, 23 Sep 2016 09:03:01 GMT

Thank You Both of You for your time and solution.

New Learning for me as well as for some others.

Regards

Cire

Re: Top and Bottom 3 Tricky

sunny_talwar — Fri, 23 Sep 2016 10:03:20 GMT

May be this:

Dimension

Code

Expression

Sum({<Code = {"=Rank(Sum({<Code={""=Sum(Amount)>20""}>}Amount)) <=3"}>}Value)

Sum({<Code = {"=Rank(Sum({<Code={""=Sum(Amount)>20""}>}Amount)) <=3"}>}Amount)

Dimension

Code

Expression

Sum({<Code = {"=Rank(-Sum({<Code={""=Sum(Amount)>20""}>}Amount)) <=3"}>}Value)

Sum({<Code = {"=Rank(-Sum({<Code={""=Sum(Amount)>20""}>}Amount)) <=3"}>}Amount)