Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
aliafshari
Contributor

Top Ten Ave

Hi

I would like to calculate the Top ten average for every item number, Suppose I have to item number like below table;How to right Top ten Av expression? I do not now something is wrong with my expression? Avg(TOTAL Aggr(If(Rank(Sum(q)<=10,sum(q),Itemnumber,customer))

    

Item numberCustomerQTop Ten Av
21021877,5002366.3
21021692,6002366.3
21021172,5002366.3
21021812,0002366.3
21021571,6602366.3
21021781,6002366.3
21022081,5502366.3
21021711,5002366.3
21021941,4032366.3
21021331,3502366.3
21021649002366.3
21021157502366.3
21021966002366.3
21021325002366.3
21021725002366.3
21021895002366.3
21021394002366.3
21021703502366.3
21021672002366.3
21022012002366.3
21021001802366.3
21021141702366.3
21022021202366.3
21021251002366.3
21021261002366.3
21021971002366.3
2102119802366.3
2102128802366.3
2102110702366.3
2102113702366.3
2102116702366.3
2102184702366.3
2102191702366.3
2102111502366.3
2102120502366.3
2102131502366.3
2102135502366.3
2102144502366.3
2102145502366.3
2102148502366.3
2102151502366.3
2102155502366.3
2102159502366.3
2102160502366.3
2102163502366.3
2102166502366.3
2102175502366.3
2102103402366.3
2102122402366.3
2102146402366.3
2102124302366.3
2102130302366.3
2102134302366.3
2102165302366.3
2102108302366.3
2102185302366.3
2102203302366.3
2102209302366.3
2102102202366.3
2102104202366.3
2102112202366.3
2102118202366.3
2102129202366.3
2102138202366.3
2102142202366.3
2102143202366.3
2102107202366.3
2102174202366.3
2102179202366.3
2102182202366.3
2102192202366.3
2102210202366.3
2102105102366.3
2102127102366.3
2102137102366.3
2102152102366.3
2102156102366.3
2102162102366.3
2102168102366.3
2102106102366.3
2102193102366.3
2102205102366.3
2102206102366.3
2102200-102366.3
410317196,00039850
410318778,00039850
410314172,00039850
410318149,20039850
410319434,90039850
410315718,00039850
410311714,40039850
410311312,00039850
410313212,00039850
410317012,00039850
410319612,00039850
410314711,10039850
41032087,20039850
41031156,60039850
41031616,00039850
41031696,00039850
41032024,80039850
41031364,20039850
41031303,60039850
41031803,30039850
41031902,70039850
41031382,40039850
41031121,50039850
41031851,50039850
41032051,50039850
41031011,20039850
41031101,20039850
41031141,20039850
41031281,20039850
41031511,20039850
41031951,20039850
41032041,20039850
410315290039850
410315390039850
410310890039850
410318390039850
410310260039850
410310360039850
410312360039850
410311660039850
410314460039850
410314560039850
410314860039850
410315060039850
410315860039850
410317460039850
410317960039850
410319860039850
410319960039850
410320760039850
410321060039850
410310930039850
410312130039850
410312430039850
410312630039850
410314630039850
410314930039850
410315430039850
410315530039850
410315630039850
410317330039850
410317630039850
410317730039850
410318630039850
410319130039850
41031406039850
41031886039850
410320060

39850

Tags (1)
8 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Top Ten Ave

Hi,

Check this link for more help.

Top N list with 2 dimensions

Display Top n by Dimension in Pivot table

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
aliafshari
Contributor

Re: Top Ten Ave

Hi kasushik solanki

In my case, I need a expression not a dimension expression.

Highlighted
devarasu07
Honored Contributor II

Re: Top Ten Ave

Hi,

use set analysis instead of aggregation, try like this

Avg(total <Itemnumber,Customer> {<Customer = {"=Rank(SUM(Q),4)<=10"}>}Q)

Capture.JPG

for the sorting u have to set

avg({<Customer = {"=Rank(SUM(Q),ItemNumber,Customer)<=10"}>}Q) at customer dimension in your sort tab. (refer attachment for the steps)


Thanks,

Deva

Highlighted
aliafshari
Contributor

Re: Top Ten Ave

Hi devarasu07

It is not work for me, can you share the QV file, please?

Highlighted
aliafshari
Contributor

Re: Top Ten Ave

It is not correct, the answer for 2102 and 4103 should be 2366 and 39850 respectively.

Highlighted
devarasu07
Honored Contributor II

Re: Top Ten Ave

Hi,

How your getting for Itemnumber (2102) =2366 ?

Capture.JPG

Highlighted
aliafshari
Contributor

Re: Top Ten Ave

sum of Top ten for 2102 is 23663 (that is:

 

7,500
2,600
2,500
2,000
1,660
1,600
1,550
1,500
1,403

1,350)

and the average is 2366 similar to 4103, sum is 398500 (that is:

 

96,000
78,000
72,000
49,200
34,900
18,000
14,400
12,000
12,000
12,000)

and average is 39850

Highlighted
MVP & Luminary
MVP & Luminary

Re: Top Ten Ave

HI,

Check the attached file.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".