topic HOWTO: Make a top 5 static? in QlikView

HOWTO: Make a top 5 static?

Tue, 18 Mar 2014 11:09:29 GMT

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!

Re: HOWTO: Make a top 5 static?

Gysbert_Wassenaar — Tue, 18 Mar 2014 11:12:46 GMT

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)&'

Re: HOWTO: Make a top 5 static?

alexandros17 — Tue, 18 Mar 2014 11:14:50 GMT

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

Re: HOWTO: Make a top 5 static?

Tue, 18 Mar 2014 11:16:40 GMT

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

Re: HOWTO: Make a top 5 static?

Tue, 18 Mar 2014 11:25:30 GMT

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)

Re: HOWTO: Make a top 5 static?

MK_QSL — Tue, 18 Mar 2014 11:34:49 GMT

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]))

Re: HOWTO: Make a top 5 static?

Tue, 18 Mar 2014 11:50:57 GMT

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.

Re: HOWTO: Make a top 5 static?

MK_QSL — Tue, 18 Mar 2014 11:55:35 GMT

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

Re: HOWTO: Make a top 5 static?

Tue, 18 Mar 2014 12:04:07 GMT

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.

Re: HOWTO: Make a top 5 static?

MK_QSL — Tue, 18 Mar 2014 12:09:13 GMT

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]))

Re: HOWTO: Make a top 5 static?

Tue, 18 Mar 2014 12:13:26 GMT

You're a star!

Many thanks.

Re: HOWTO: Make a top 5 static?

MK_QSL — Tue, 18 Mar 2014 12:15:38 GMT

Glad that my solutions helps you.

I am just like other members... still learning ! 🙂