8 Replies Latest reply: Nov 27, 2017 6:57 AM by Sunny Talwar

Sum of Filed on a condition of different field ?

Hi All

I want to sum the Volume who has Max Price group by Material Vendor combination and show in KPI

For example

I have the following table

MaterialVendorPriceVolume
1A10100
1A20200
2B40300
2B50400

Expected Output  in KPI .

For Material 1 and Vendor A  the max price is 20 , so we have to consider 200 as Volume for that Material Vendor Combination

same goes for

Material 2 and Vendor 2 the max price is 50 , so we have to consider 400 as Volume for that Material Vendor Combination

So , in kpi  the value should show 600

200 + 400 = 600

Can anyone help me in this ?

Thanks in Advance

Regards

Gaurav Gopale

• Re: Sum of Filed on a condition of different field ?

May be this

=Sum(Aggr(FirstSortedValue(Volume,-Price),Material,Vendor))

Regards,

Antonio

• Re: Sum of Filed on a condition of different field ?

Hi

antonio, without selecting material the value is coming .

But, when we select material i is showing zero value

• Re: Sum of Filed on a condition of different field ?

Another option would be this

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Volume), Sum(Volume)), Material, Vendor, Price))

or this

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Volume), Volume), Material, Vendor, Price))

• Re: Sum of Filed on a condition of different field ?

Hi sunny ,

it is showing zero value

• Re: Sum of Filed on a condition of different field ?

My bad, I had typos

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Price), Sum(Volume)), Material, Vendor, Price))

and this

Sum(Aggr(If(Price = Max(TOTAL <Material, Vendor> Price), Volume), Material, Vendor, Price))

• Re: Sum of Filed on a condition of different field ?

Hi

Sunny and Antonio , thanks for the reply

The following expression in working.

sum(

Aggr(If(PRICE_MAT_New = Max(TOTAL <MATERIAL,VENDOR> PRICE_MAT_New),

FirstSortedValue(VOLUME,-VOLUME

)

), MATERIAL,VENDOR, PRICE_MAT_New)

)

• Re: Sum of Filed on a condition of different field ?

Awesome, we are glad you were able to figure it out yourself. Please close this thread by marking your own response as correct.

Best,

Sunny