Need help with aggregatiom

[irmantas]

Hi,

I have invent trans table, like:

ItemId	TransType	TrasRefId	Qty
PA-001	Prod Line	prod001	-25
PA-002	Prod Line	prod001	-2
PA-003	Prod Line	prod001	-13
PA-004	Prod Line	prod001	-20
PA-005	Prod Line	prod001	-25
PA-006	Prod Line	prod001	-25
PR-001	Prod	prod001	25
PA-001	Prod Line	prod002	-13
PA-002	Prod Line	prod002	-1
PA-003	Prod Line	prod002	-5
PA-004	Prod Line	prod002	-10
PA-005	Prod Line	prod002	-13
PA-006	Prod Line	prod002	-13
PR-002	Prod	prod002	13

As you can see, I have 2 types of trans - ProdLine (consumption of raw materials) and Prod (Product revenue). I want find consumption of raw material (ItemId), and at the same to find in how many products it used. Like:

ItemId	Consumption	Products
PA-003	-18	38

Were Consumption = (-13)+(-5) and Products = 25+13

With consumption no problem by selecting ItemId PA-003 in dimensions, but problem is to sum all Products in with was used PA-003

Please help me with this.

[irmantas]

Oliver, one more problem:)

the formula works only in ideal conditions, but if we have some corrections in Prod ex:

ItemId	TransType	TrasRefId	Qty
PR-001	Prod	prod001	25
PR-001	Prod	prod001	-25
PR-001	Prod	prod001	25

Sum of QTY is 25, but  sum(aggr(nodistinct sum( {1<TransType = {Prod} >}  Qty),TrasRefId)) = 75

How I can fix it?

[irmantas]

I still have problem on it:

the formula works only in ideal conditions, but if we have some corrections in Prod ex:

ItemId	TransType	TrasRefId	Qty
PR-001	Prod	prod001	25
PR-001	Prod	prod001	-25
PR-001	Prod	prod001	25

Sum of QTY is 25, but  sum(aggr(nodistinct sum( {1<TransType = {Prod} >}  Qty),TrasRefId)) = 75

How I can fix it?

[ogautier62]

what is your expression for qty ?

if qty is the field sum( .......   qty) gives 25 not 75

you must put in aggr(sum same expression as used to display qty

[irmantas]

Olivier, sorry, but I am new in Qlik and do not understand your question about "what is your expression for qty?".

I Just add more 2 lines in first my example:

LOAD * Inline [ItemId,TransType,TrasRefId,Qty

PA-001,Prod Line,prod001,-25

PA-002,Prod Line,prod001,-2

PA-003,Prod Line,prod001,-13

PA-004,Prod Line,prod001,-20

PA-005,Prod Line,prod001,-25

PA-006,Prod Line,prod001,-25

PR-001,Prod,prod001,25

PR-001,Prod,prod001,-25

PR-001,Prod,prod001,25

PA-001,Prod Line,prod002,-13

PA-002,Prod Line,prod002,-1

PA-003,Prod Line,prod002,-5

PA-004,Prod Line,prod002,-10

PA-005,Prod Line,prod002,-13

PA-006,Prod Line,prod002,-13

PR-002,Prod,prod002,13

];

And using the same formulas:

=sum({<TransType={'Prod Line'}  >} Qty)

= sum(aggr(nodistinct sum( {<TransType = {Prod} >}  Qty),TrasRefId))

But result is wrong:

[irmantas]

Olivier, sorry, but I am new in Qlik and do not understand your question about "what is your expression for qty?".

I Just add more 2 lines in first my example:

LOAD * Inline [ItemId,TransType,TrasRefId,Qty

PA-001,Prod Line,prod001,-25

PA-002,Prod Line,prod001,-2

PA-003,Prod Line,prod001,-13

PA-004,Prod Line,prod001,-20

PA-005,Prod Line,prod001,-25

PA-006,Prod Line,prod001,-25

PR-001,Prod,prod001,25

PR-001,Prod,prod001,-25

PR-001,Prod,prod001,25

PA-001,Prod Line,prod002,-13

PA-002,Prod Line,prod002,-1

PA-003,Prod Line,prod002,-5

PA-004,Prod Line,prod002,-10

PA-005,Prod Line,prod002,-13

PA-006,Prod Line,prod002,-13

PR-002,Prod,prod002,13

];

And using the same formulas:

=sum({<TransType={'Prod Line'}  >} Qty)

= sum(aggr(nodistinct sum( {<TransType = {Prod} >}  Qty),TrasRefId))

But result is wrong:

[ogautier62]

Hi,

ok formula must be different in this case

the sum of qty is 25, but because you have three rows, sum is made three times : 3 x 25 = 75

is this a real use case ?

if so : when transtype is Prod: just sum({<xxxxxxxxx>} Qty)

and not sum(aggr(sum

if(TransType = 'Prod',

sum( {<TransType = {Prod} >} Qty)

, sum(aggr(nodistinct sum( {<TransType = {Prod} >}  Qty),TrasRefId))

)