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.

[prma7799]

Try this

sum({ <ProductId=P(InventId)>} StockQty)

[tamilarasu]

Hi Irmantas,

Try

Sum(IF(ProductId=InventId,StockQty) )

[shiveshsingh]

please try this way

[irmantas]

Thank you PM it's works.

What does it mean "P" in P(InventId) ?

[irmantas]

Problem in duplicating StokQty, when Sales is more then one for the same ProductId.

[irmantas]

Sorry PM I was made mistake, but it's do not work. With this I get sum of all StockQty. As in Ex. 150

[irmantas]

Duplicating sum of StockQTY.

In ex: if ProductId "X" have more entries, StockQTY is sum of 100*(num of entries)

[shiveshsingh]

Can you share sample data which is resulting in duplicacy?

[irmantas]

My real load data:

In pivot StockQTY expression is sum(if(InventId=ProductId,(StockQTY)))

As we can see SalesQTY have 5 entries. StockQTY 9293,220=1858,644*5

How I can reach this problem?