2 Replies Latest reply: Jan 30, 2017 11:39 AM by Giovanni Leonardi

# Aggregation in Qlik Sense shows unexpected values

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:

WarehouseItemStock BalanceOrdered QtyAvailable Qty
MAINA10120
MAINB15141
MAINC202
Total27263
SECONDARYB105

5

SECONDARYC5110
Total15165

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:

WarehouseAvailable Qty
MAIN1
SECONDARY0

But i would like to be:

Warehouse
Available Qty
MAIN3
SECONDARY5

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

• ###### Re: Aggregation in Qlik Sense shows unexpected values

Try this may be:

Sum(Aggr(

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

, Warehouse, Item))

• ###### Re: Aggregation in Qlik Sense shows unexpected values

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