Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.....
Please find my sample data
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)
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
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])
try this
=Sum({<Material={"=Count(Distinct {< SCOPE={'Yes'}>}[VENDOR CODE])>1"}>}SPEND)
or
sum(aggr(if(Count(Distinct {< SCOPE={'Yes'}>}[VENDOR CODE])>1,SPEND),Material,[VENDOR CODE]))