# 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
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 number Customer Q Top Ten Av 2102 187 7,500 2366.3 2102 169 2,600 2366.3 2102 117 2,500 2366.3 2102 181 2,000 2366.3 2102 157 1,660 2366.3 2102 178 1,600 2366.3 2102 208 1,550 2366.3 2102 171 1,500 2366.3 2102 194 1,403 2366.3 2102 133 1,350 2366.3 2102 164 900 2366.3 2102 115 750 2366.3 2102 196 600 2366.3 2102 132 500 2366.3 2102 172 500 2366.3 2102 189 500 2366.3 2102 139 400 2366.3 2102 170 350 2366.3 2102 167 200 2366.3 2102 201 200 2366.3 2102 100 180 2366.3 2102 114 170 2366.3 2102 202 120 2366.3 2102 125 100 2366.3 2102 126 100 2366.3 2102 197 100 2366.3 2102 119 80 2366.3 2102 128 80 2366.3 2102 110 70 2366.3 2102 113 70 2366.3 2102 116 70 2366.3 2102 184 70 2366.3 2102 191 70 2366.3 2102 111 50 2366.3 2102 120 50 2366.3 2102 131 50 2366.3 2102 135 50 2366.3 2102 144 50 2366.3 2102 145 50 2366.3 2102 148 50 2366.3 2102 151 50 2366.3 2102 155 50 2366.3 2102 159 50 2366.3 2102 160 50 2366.3 2102 163 50 2366.3 2102 166 50 2366.3 2102 175 50 2366.3 2102 103 40 2366.3 2102 122 40 2366.3 2102 146 40 2366.3 2102 124 30 2366.3 2102 130 30 2366.3 2102 134 30 2366.3 2102 165 30 2366.3 2102 108 30 2366.3 2102 185 30 2366.3 2102 203 30 2366.3 2102 209 30 2366.3 2102 102 20 2366.3 2102 104 20 2366.3 2102 112 20 2366.3 2102 118 20 2366.3 2102 129 20 2366.3 2102 138 20 2366.3 2102 142 20 2366.3 2102 143 20 2366.3 2102 107 20 2366.3 2102 174 20 2366.3 2102 179 20 2366.3 2102 182 20 2366.3 2102 192 20 2366.3 2102 210 20 2366.3 2102 105 10 2366.3 2102 127 10 2366.3 2102 137 10 2366.3 2102 152 10 2366.3 2102 156 10 2366.3 2102 162 10 2366.3 2102 168 10 2366.3 2102 106 10 2366.3 2102 193 10 2366.3 2102 205 10 2366.3 2102 206 10 2366.3 2102 200 -10 2366.3 4103 171 96,000 39850 4103 187 78,000 39850 4103 141 72,000 39850 4103 181 49,200 39850 4103 194 34,900 39850 4103 157 18,000 39850 4103 117 14,400 39850 4103 113 12,000 39850 4103 132 12,000 39850 4103 170 12,000 39850 4103 196 12,000 39850 4103 147 11,100 39850 4103 208 7,200 39850 4103 115 6,600 39850 4103 161 6,000 39850 4103 169 6,000 39850 4103 202 4,800 39850 4103 136 4,200 39850 4103 130 3,600 39850 4103 180 3,300 39850 4103 190 2,700 39850 4103 138 2,400 39850 4103 112 1,500 39850 4103 185 1,500 39850 4103 205 1,500 39850 4103 101 1,200 39850 4103 110 1,200 39850 4103 114 1,200 39850 4103 128 1,200 39850 4103 151 1,200 39850 4103 195 1,200 39850 4103 204 1,200 39850 4103 152 900 39850 4103 153 900 39850 4103 108 900 39850 4103 183 900 39850 4103 102 600 39850 4103 103 600 39850 4103 123 600 39850 4103 116 600 39850 4103 144 600 39850 4103 145 600 39850 4103 148 600 39850 4103 150 600 39850 4103 158 600 39850 4103 174 600 39850 4103 179 600 39850 4103 198 600 39850 4103 199 600 39850 4103 207 600 39850 4103 210 600 39850 4103 109 300 39850 4103 121 300 39850 4103 124 300 39850 4103 126 300 39850 4103 146 300 39850 4103 149 300 39850 4103 154 300 39850 4103 155 300 39850 4103 156 300 39850 4103 173 300 39850 4103 176 300 39850 4103 177 300 39850 4103 186 300 39850 4103 191 300 39850 4103 140 60 39850 4103 188 60 39850 4103 200 60 39850
Tags (1)
8 Replies
Highlighted
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
Highlighted
Contributor

## Re: Top Ten Ave

Hi kasushik solanki

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

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

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
Contributor

## Re: Top Ten Ave

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

Highlighted
Contributor

## Re: Top Ten Ave

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

Highlighted
Honored Contributor II

## Re: Top Ten Ave

Hi,

How your getting for Itemnumber (2102) =2366 ?

Highlighted
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

## Re: Top Ten Ave

HI,

Check the attached file.

Regards,

Kaushik Solanki

Regards,
Kaushik