Aggregate: going crazy!

qlikviewaf — Fri, 14 Apr 2023 09:53:43 GMT

Hello,

as you can se bellow i created 2 chart - the first one got 2 dimension (Item and Components)

On the second one i removed the Item and i just left the Components.

I have an issue with fIeld Implant Need: i would like to have "12" also as result on the second table.

The field Implant need is calculated as follow: If Order Quantity is > than Implant Stock, then Order Quantity-Implant Stock.

if(sum(ORDERQuantity)-sum(IMPLANTSTOCK)>0,sum(ORDERQuantity)-sum(IMPLANTSTOCK),0)

to have 12 also on the second table - so to have the table working by "Item" - what i should do?
Please help!!
Thank you!!

Re: Aggregate: going crazy!

Kushal_Chawda — Fri, 14 Apr 2023 10:21:35 GMT

@qlikviewaf Your calculation works at Item and Component level, but in second table you don't have Item, so your actual calculation works as if(12-247 >0,...) which will not work as expected, so you need to user aggr function to work your calculation at Item and Component level

sum(aggr(if(sum(ORDERQuantity)-sum(IMPLANTSTOCK)>0,sum(ORDERQuantity)-sum(IMPLANTSTOCK),0), Item, Component))

topic Aggregate: going crazy! in QlikView

Aggregate: going crazy!

Re: Aggregate: going crazy!