Aggregation in Qlik Sense shows unexpected values

[#1]

Hello everybody, this is my first post and I come to you with a smart question.

I'm searching for a solution to this problem: I have a set of data coming from stock balances. My app must show the amount of available quantities for each Item.

The simplest expression would be [Stock Qty] - [Ordered Qty] = [Available Qty] that will be coded as  Sum ([Stock Qty] - [Ordered Qty]).

Now comes the tricky part. Since I don't want any negative value my expression should be:

if ((Sum ([Stock Qty] - [Ordered Qty]) < 0, 0, Sum ([Stock Qty] - [Ordered Qty])) and at the lower aggregation level it works fine, but when it comes to do it to a higher level it doesn't make the job done.

I have this scenario:

Warehouse	Item	Stock Balance	Ordered Qty	Available Qty
MAIN	A	10	12	0
MAIN	B	15	14	1
MAIN	C	2	0	2
Total		27	26	3
SECONDARY	B	10	5	5
SECONDARY	C	5	11	0
Total		15	16	5

If I create a pivot table or a Bar Chart based on Warehouse field in Dimension and Available Qty field in measure, the result is the following:

Warehouse	Available Qty
MAIN	1
SECONDARY	0

But i would like to be:

Warehouse	Available Qty
MAIN	3
SECONDARY	5

Is there a way to tell the system to always sum at the lowest level to obtain the above values.

I hope I made myself clear.

Regards,

Giovanni

[sunny_talwar · Accepted solution]

Try this may be:

Sum(Aggr(

if ((Sum ([Stock Qty] - [Ordered Qty]) < 0, 0, Sum ([Stock Qty] - [Ordered Qty]))

, Warehouse, Item))

[#3]

Thank you Sunny for your quick reply, I was not familiar with the aggr() function. I read about it on reference manuals, but not sure on how to apply it to my projects, now it looks more clear.

I tried it in my app and it seems to workfine.

Thanx a lot

Giovanni