topic Re: SUM with IF in QlikView

SUM with IF

irmantas — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: SUM with IF

prma7799 — Wed, 14 Feb 2018 07:17:48 GMT

Try this

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

Re: SUM with IF

tamilarasu — Wed, 14 Feb 2018 07:19:32 GMT

Hi Irmantas,

Try

Sum(IF(ProductId=InventId,StockQty) )

Re: SUM with IF

shiveshsingh — Wed, 14 Feb 2018 07:43:59 GMT

please try this way

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 08:19:39 GMT

Thank you PM it's works.

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

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 08:20:51 GMT

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

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 08:31:40 GMT

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

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 08:36:48 GMT

Duplicating sum of StockQTY.

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

Re: SUM with IF

shiveshsingh — Wed, 14 Feb 2018 08:58:59 GMT

Can you share sample data which is resulting in duplicacy?

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 09:10:14 GMT

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?

Re: SUM with IF

shiveshsingh — Wed, 14 Feb 2018 09:34:25 GMT

Try this

=SUM({<ProductId = {$(=InventID)}>}StockQty)

Re: SUM with IF

prma7799 — Wed, 14 Feb 2018 09:38:53 GMT

Please share some sample data.

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 10:00:04 GMT

Not working, sums all StockQTY of all InventId

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 10:00:56 GMT

See up I was shared.

Re: SUM with IF

shiveshsingh — Wed, 14 Feb 2018 10:15:06 GMT

Let me check, i thought you are having only one inventID

Re: SUM with IF

tamilarasu — Wed, 14 Feb 2018 10:33:34 GMT

Could you draw expected output format for below example?

Re: SUM with IF

PrashantSangle — Wed, 14 Feb 2018 10:37:39 GMT

Hi,

try with aggr() and distinct

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

Re: SUM with IF

irmantas — Wed, 14 Feb 2018 11:05:29 GMT

What does it mean "dimesionfield"?

Re: SUM with IF

prma7799 — Wed, 14 Feb 2018 11:09:33 GMT

dimesionfield (simply your Dimension )

Check this

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

Re: SUM with IF

prma7799 — Wed, 14 Feb 2018 11:10:55 GMT

Check this

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/aggr.htm