Top Ten Ave

[Anonymous]

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

[kaushiknsolanki]

Hi,

Check this link for more help.

Top N list with 2 dimensions

Display Top n by Dimension in Pivot table

Regards,

Kaushik Solanki

[Anonymous]

Hi kasushik solanki

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

[devarasu07]

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

[Anonymous]

Hi devarasu07

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

[Anonymous]

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

[devarasu07]

Hi,

How your getting for Itemnumber (2102) =2366 ?

[Anonymous]

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

[kaushiknsolanki]

HI,

Check the attached file.

Regards,

Kaushik Solanki