SUM with IF

[irmantas]

Hi all,

I have two tables without a shared key.

ProductId	SalesQty
X	5
Y	2
Z	6

InventId	StockQty
X	100
Y	50

In Pivot I want show sum of SalesQTY and sum of StackQTY. Were ProducId = InventId.

sum({ <ProductId=InventId>} StockQty) is not working.

[irmantas]

Something wrong. SUM StockQTY rising up in every row.

[shiveshsingh]

Check if full accumulation is off in chart properties.

[prma7799]

try this

aggr(Distinct sum(if(InventId=ProductId,(StockQTY))),InventId)

or

aggr(Distinct sum(if(InventId=ProductId,(StockQTY))),InventId,ProductId)

[irmantas]

"No accumulation" helps, do not rising up. But still sum of StockQTY is wrong:

[shiveshsingh]

Which expression are you using?

[irmantas]

aggr(Distinct sum(if(InventId=ProductId,(StockQTY))),ProductId)

[PrashantSangle]

If possible share your app..