15 Replies Latest reply: Aug 11, 2017 9:22 AM by Sunny Talwar

# number of parts with multiple prices with same supplier

Hi All,

the KIP i need to show is

# parts with multiple prices with same supplier

what i understand is , i need to show number of materials  which is supplied by same supplier having more then one distinct  price and validto has to be greater than today()

Dimension :Supplier

Expression:

aggr(Count({<SCOPE={'Yes'},VALIDTO={">\$(=Date(today(),'DD-MM-YYYY'))"},supplier={"=Count(Distinct Price)>1"}>}distinct part),supplier)

i am getting materials , suppliers which has only one  price ,which should not ,so i think  the greater than syntax in my expression

has problem,

any  suggestion plz..

Thanks

• ###### Re: number of parts with multiple prices with same supplier

try below put "= "

aggr(Count({<SCOPE={'Yes'},VALIDTO={">=\$(=Date(today(),'DD-MM-YYYY'))"},supplier={"=Count(Distinct Price)>1"}>}distinct part),supplier)

• ###### Re: number of parts with multiple prices with same supplier

1) I don't think you need Aggr() in your expression

2) May be try this

Count({<SCOPE = {'Yes'}, VALIDTO = {">\$(=Date(today(),'DD-MM-YYYY'))"}, supplier = {"=Count({<SCOPE={'Yes'}, VALIDTO ={"">\$(=Date(today(),'DD-MM-YYYY'))""}>} DISTINCT Price)>1"}>} DISTINCT part)

• ###### Re: number of parts with multiple prices with same supplier

i have used aggr because in different KIP i need to group it with plant wise ,so without using aggr how can i able to group with plant

Count({<SCOPE = {'Yes'}, VALIDTO = {">\$(=Date(today(),'DD-MM-YYYY'))"}, supplier = {"=Count({<SCOPE={'Yes'}, VALIDTO ={"">\$(=Date(today(),'DD-MM-YYYY'))""}>} DISTINCT Price)>1"}>} DISTINCT<Plant> part)

is above expression a valid one

• ###### Re: number of parts with multiple prices with same supplier

Group by plant? Not sure I understand? can you elaborate?

• ###### Re: number of parts with multiple prices with same supplier

i mean for a  particular plant ,supplier combination how many materials has distinct multiple prices supplied by that supplier .

• ###### Re: number of parts with multiple prices with same supplier

Then I believe you will have to use Aggr() function instead of set analysis

• ###### Re: number of parts with multiple prices with same supplier

Hello , i have used the below expression which suggested by you

Count({<SCOPE = {'Yes'},MATL_TYPE={'HALB','ROH'}, VALIDTO = {">\$(=Date(today(),'DD-MM-YYYY'))"}, [MATERIAL CODE]= {"=Count({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'}, VALIDTO ={"">\$(=Date(today(),'DD-MM-YYYY'))""}>} DISTINCT PRICE_MAT)>1"}>} DISTINCt [MATERIAL CODE])

before selecting vendor i am getting wrong output

after selecting that vendor i am getting correct o/p

can u suggest me y this is happening

• ###### Re: number of parts with multiple prices with same supplier

Looking at screenshots and expressions doesn't give me much details Naveen. Would you be able to share a sample to dig deeper. Also, it would be helpful to know the expected number you are looking to get with and without selections

• ###### Re: number of parts with multiple prices with same supplier

i wish to do so but it is client data which i am not allowed to share ,to keep it simple the number i am getting after selecting vendor (1,2)should appear even if i don't select vendor because they are correct num

• ###### Re: number of parts with multiple prices with same supplier

With or without selection you want to get 1, 2?

• ###### Re: number of parts with multiple prices with same supplier

it should not change before and after selection

• ###### Re: number of parts with multiple prices with same supplier

do i need to  put vendor in the expression ?because what i think is after selecting vendor i am getting correct o/p which mean i need to filter expression with vendor ,do i need to do  group by vendor ?

• ###### Re: number of parts with multiple prices with same supplier

Not sure, but try this

Count({<SCOPE = {'Yes'}, MATL_TYPE = {'HALB','ROH'}, VALIDTO = {">\$(=Date(today(),'DD-MM-YYYY'))"}>} Aggr(If(Count({<SCOPE={'Yes'}, MATL_TYPE={'HALB','ROH'}, VALIDTO ={">\$(=Date(today(),'DD-MM-YYYY'))"}>} DISTINCT PRICE_MAT) > 1, Only({<SCOPE={'Yes'}, MATL_TYPE={'HALB','ROH'}, VALIDTO ={">\$(=Date(today(),'DD-MM-YYYY'))"}>}[MATERIAL CODE])), [MATERIAL CODE], Vendor))

• ###### Re: number of parts with multiple prices with same supplier

i known u gona hate me today ,i tried ur expression it is working thanks,but same expression i have used by changing material code to price ,then i am getting zero

Count({<SCOPE ={'Yes'}, MATL_TYPE = {'HALB','ROH'}, VALIDTO = {">\$(=Date(today(),'DD-MM-YYYY'))"}>} Aggr(If(Count({<SCOPE={'Yes'}, MATL_TYPE={'HALB','ROH'}, VALIDTO ={">\$(=Date(today(),'DD-MM-YYYY'))"}>} DISTINCT PRICE_MAT) > 1, Only({<SCOPE={'Yes'}, MATL_TYPE={'HALB','ROH'}, VALIDTO ={">\$(=Date(today(),'DD-MM-YYYY'))"}>}distinct PRICE_MAT)), [MATERIAL CODE], [VENDOR CODE]))

i should get Distinct Price =2

• ###### Re: number of parts with multiple prices with same supplier

Try this

Sum({<SCOPE ={'Yes'}, MATL_TYPE = {'HALB','ROH'}, VALIDTO = {">\$(=Date(today(),'DD-MM-YYYY'))"}>} Aggr(If(Count({<SCOPE={'Yes'}, MATL_TYPE={'HALB','ROH'}, VALIDTO ={">\$(=Date(today(),'DD-MM-YYYY'))"}>} DISTINCT PRICE_MAT) > 1, Count({<SCOPE={'Yes'}, MATL_TYPE={'HALB','ROH'}, VALIDTO ={">\$(=Date(today(),'DD-MM-YYYY'))"}>}DISTINCT PRICE_MAT)), [MATERIAL CODE], [VENDOR CODE]))