how to write expression

[kunkumnaveen]

Hello All,

                 i am able to achieve my requirement  partially . the thing is i am trying to display only those materials,spend which has supplied by multiple vendors

Expressions i tried :

1)Sum({<SCOPE={'Yes'},Material={"=Count(Distinct [VENDOR CODE)>1"}>}SPEND)

2)If(Count(distinct TOTAL <Material> [VENDOR CODE])>1,Sum({<SCOPE={'Yes'}>}SPEND))

but both the expressions are giving me list of  multiple vendors and single vendors materials , as below

All the 100% rows are  single vendor supplied material

so how to not display this 100% rows

Any suggestion plz.....

[kunkumnaveen]

Please find my sample data

[effinty2112]

Dear Naven,

Hera are the first few rows of a straight table that may be what you want.

Material	Total	Count of Vendor
	146952889.27	31
0000000A7	13637439.28	4
000A490	668592.72	2
000A760	481471.20	2
000A852	78765439.71	4
000A865	3129844.20	2
000A866	12064547.66	3
00A6156	68062.20	2
00A6360	488119.62	2
0A11775	273441.19	2
0A0012268P04	2926115.31	2
0A0020115E05	33124364.88	3
0A0050857D01	1325451.30	3

Total: aggr(if(Count([VENDOR CODE])>1, Sum(Spend1)),Material)

Count of Vendor: aggr(if(Count([VENDOR CODE])>1, Count([VENDOR CODE])),Material)

[kunkumnaveen]

Thanks For  your kindly help, but walker ,for each material ur showing aggr value what I am looking is individual value for each material rather then aggr value

[effinty2112]

Naveen,

Do you want the sum for each vendor for each product?

try:

Material	VENDOR CODE	Expr
		93552942.91
0000000A7	DG028A	4389941.03
0000000A7	DI009	5585682.71
0000000A7	DS093	1649215.70
0000000A7	DS093C	2012599.84
000A490	DP064	650880.72
000A490	PLMA01	17712.00
000A760	DM071	118352.62
000A760	DS109	363118.58
000A852	DD019	15131714.22
000A852	DE004	23883222.60
000A852	DE026	32170113.22
000A852	DS064	7580389.67

Where Expr : aggr(NODISTINCT if(Count(TOTAL<Material> [VENDOR CODE])>1, Sum(Spend1)),Material,[VENDOR CODE])

[Kushal_Chawda]

try this

=Sum({<Material={"=Count(Distinct {< SCOPE={'Yes'}>}[VENDOR CODE])>1"}>}SPEND)

[Kushal_Chawda]

or

sum(aggr(if(Count(Distinct {< SCOPE={'Yes'}>}[VENDOR CODE])>1,SPEND),Material,[VENDOR CODE]))