topic Re: Need help with aggregatiom in QlikView

Need help with aggregatiom

irmantas — Thu, 30 Aug 2018 07:40:26 GMT

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.

Re: Need help with aggregatiom

menta — Thu, 30 Aug 2018 08:02:10 GMT

You can use:

SUM(total {1<TransType={Prod}>}Qty)

SUM(total {<TransType={Prod},ItemId>}Qty)

Re: Need help with aggregatiom

avkeep01 — Thu, 30 Aug 2018 08:03:29 GMT

Hi Irmantas Tubutis,

I've created an expression based on the case below. It shows the sum of qty for the TransType = "Prod". It ignores the selection on ItemId.

=SUM(TOTAL {$<TransType={"Prod"},ItemId=>} Qty)

This is the result:

ItemId	Consumption	Production
PA-001	-38	38
PA-002	-3	38
PA-003	-18	38

But I'm not sure if this is what you want. Because every row has the value 38 for consumption.

Re: Need help with aggregatiom

ogautier62 — Thu, 30 Aug 2018 08:08:05 GMT

Hi,

as dimensions :

ItelId, TrasRefId

expressions :

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

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

regards

Re: Need help with aggregatiom

irmantas — Thu, 30 Aug 2018 08:25:01 GMT

Thanks for answer, but both formulas calculating all of Prod Qty, but I need only these Prod with was used selected ItemId.

Re: Need help with aggregatiom

ogautier62 — Thu, 30 Aug 2018 08:31:03 GMT

it's ok for me

(I add an item)

it was not used in calcultion for item003

Re: Need help with aggregatiom

irmantas — Thu, 30 Aug 2018 08:50:45 GMT

Working:) But if I select one dimension PA-003 , Prod =0

Re: Need help with aggregatiom

ogautier62 — Thu, 30 Aug 2018 08:54:50 GMT

add {1 :

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

Re: Need help with aggregatiom

irmantas — Thu, 30 Aug 2018 10:02:05 GMT

Thank you, looks like working.

Re: Need help with aggregatiom

ogautier62 — Thu, 30 Aug 2018 10:02:42 GMT

ok thank's

good day

Re: Need help with aggregatiom

irmantas — Thu, 30 Aug 2018 11:37:38 GMT

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?

Re: Need help with aggregatiom

irmantas — Thu, 06 Sep 2018 12:01:28 GMT

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?

Re: Need help with aggregatiom

ogautier62 — Thu, 06 Sep 2018 12:15:27 GMT

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

Re: Need help with aggregatiom

irmantas — Thu, 13 Sep 2018 07:55:19 GMT

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:

Re: Need help with aggregatiom

irmantas — Thu, 13 Sep 2018 11:37:48 GMT

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:

Re: Need help with aggregatiom

ogautier62 — Thu, 13 Sep 2018 15:54:38 GMT

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))

)