Top and Bottom 3 Tricky

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

‌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.

[sunny_talwar]

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?

[#5]

Hi Sir

I want this Output:

[sunny_talwar]

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

[#7]

‌

‌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.

[Gysbert_Wassenaar · Accepted solution]

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

[#9]

Thank You Both of You for your time and solution.

New Learning for me as well as for some others.

Regards

Cire

[sunny_talwar]

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)