Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gauravgg
Partner - Creator
Partner - Creator

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



8 Replies
antoniotiman
Master III
Master III

May be this

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

Regards,

Antonio

sunny_talwar

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))

gauravgg
Partner - Creator
Partner - Creator
Author

Hi

antonio, without selecting material the value is coming .

But, when we select material i is showing zero value

gauravgg
Partner - Creator
Partner - Creator
Author

Hi sunny ,

it is showing zero value

antoniotiman
Master III
Master III

sunny_talwar

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))

gauravgg
Partner - Creator
Partner - Creator
Author

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)

)

sunny_talwar

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