HOWTO: Make a top 5 static?

[#1]

Hi everybody,

I'm trying to make a top 5 idependent of the current selections in a text box but I'm a bit stuck.

How could I alter this expression so that it remains static?

AGGR(IF(RANK(SUM(Contractors.Test_Cost))=1,Contractors.Fullname&': '&num(SUM(Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)&'

'&
AGGR(IF(RANK(SUM(Contractors.Test_Cost))=2,Contractors.Fullname&': '&num(SUM(Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)&'

'&
AGGR(IF(RANK(SUM(Contractors.Test_Cost))=3,Contractors.Fullname&': '&num(SUM(Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)&'

'&
AGGR(IF(RANK(SUM(Contractors.Test_Cost))=4,Contractors.Fullname&': '&num(SUM(Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)&'

'&
AGGR(IF(RANK(SUM(Contractors.Test_Cost))=5,Contractors.Fullname&': '&num(SUM(Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)

I might be doing it the wrong way...

Thanks!

[Gysbert_Wassenaar]

If by static you mean independent of selections then add {1} in every sum:

AGGR(IF(RANK(SUM({1}Contractors.Test_Cost))=1,Contractors.Fullname&': '&num(SUM({1}Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)&'

[alexandros17]

If static means that values donìt change with selection you need to exclude in each sum the fields taht could be used:

SUM({$ <field1=, field2= >} Contractors.Test_Cost) in this way for any selection of field 1 and 2 values don't change

[#4]

Yes I thought that would be the solution but it's not working. I'm on QV10.

If I select fields such as %Cost_centre or a division this expression is impacted for some reason...

[#5]

Just tried: 

AGGR(IF(RANK(SUM({$ <%Cost_centre=, Contractors.Division=,Contractors.Fullname= >} Contractors.Test_Cost))=5,Contractors.Fullname&': '&num(SUM({$ <%Cost_centre=, Contractors.Division=,Contractors.Fullname= >}Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)

It returns null when I select a Cost centre...

So does:

AGGR

(IF(RANK(SUM({1}Contractors.Test_Cost))=1,Contractors.Fullname&': '&num(SUM({1}Contractors.Test_Cost),'£#,##0.##')),Contractors.Fullname)

[MK_QSL · Accepted solution]

Try Something like below... Change your field names accordingly.

=CONCAT({1}DISTINCT IF(Aggr(Rank(SUM({1}Sales),4),[Customer Name])<=5,[Customer Name]),' '&CHR(13),

  -AGGR(SUM({1}Sales),[Customer Name]))

[#7]

Yep, that worked.

=CONCAT({1}DISTINCT IF(AGGR(RANK(SUM({1}Contractors.Test_Cost),4),Contractors.Fullname)<=5,Contractors.Fullname),'
',
-AGGR(SUM({1}Contractors.Test_Cost),Contractors.Fullname))

Now I need to understand it!

Thanks.

[MK_QSL]

=CONCAT({1}DISTINCT IF(AGGR(RANK(SUM({1}Contractors.Test_Cost),4),Contractors.Fullname)<=5,Contractors.Fullname),'
',
-AGGR(SUM({1}Contractors.Test_Cost),Contractors.Fullname))

We have FIXED SUM and CONCAT Both...

IF(AGGR(RANK(SUM({1}Contractors.Test_Cost),4),Contractors.Fullname)<=5,Contractors.Fullname)

Will check Rank for First 5

Now We have CONCAT the result of above with separator ' ' 

-AGGR(SUM({1}Contractors.Test_Cost),Contractors.Fullname)

Will Sort the CONCAT order by SUM of Your desired dimension.

[#9]

The only down side is that I cannot display the Contractor cost with this method.

I wonder if there's a way to do it.

[MK_QSL]

Something Like This?

=CONCAT({1}DISTINCT

     IF(Aggr(Rank(SUM({1}Sales),4),[Customer Name])<=5,

          Aggr([Customer Name]&' '&SUM(Sales),[Customer Name])),' '&CHR(13),

  -AGGR(SUM({1}Sales),[Customer Name]))