Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
irmantas
Contributor III
Contributor III

Need help with aggregatiom

Hi,

I have invent trans table, like:

    

ItemIdTransTypeTrasRefIdQty
PA-001Prod Lineprod001-25
PA-002Prod Lineprod001-2
PA-003Prod Lineprod001-13
PA-004Prod Lineprod001-20
PA-005Prod Lineprod001-25
PA-006Prod Lineprod001-25
PR-001Prodprod00125
PA-001Prod Lineprod002-13
PA-002Prod Lineprod002-1
PA-003Prod Lineprod002-5
PA-004Prod Lineprod002-10
PA-005Prod Lineprod002-13
PA-006Prod Lineprod002-13
PR-002Prodprod00213

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:

   

ItemIdConsumptionProducts
PA-003-1838

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.

15 Replies
menta
Partner - Creator II
Partner - Creator II

You can use:

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

Or

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

avkeep01
Partner - Specialist
Partner - Specialist

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:

ItemIdConsumptionProduction
PA-001-3838
PA-002-338
PA-003-1838

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

ogautier62
Specialist II
Specialist II

Hi,

as dimensions :

ItelId, TrasRefId

expressions :

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

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

regards

irmantas
Contributor III
Contributor III
Author

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

ogautier62
Specialist II
Specialist II

it's ok for me

(I add an item)

it was not used in calcultion for item003

irmantas
Contributor III
Contributor III
Author

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

ogautier62
Specialist II
Specialist II

add {1 :

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

irmantas
Contributor III
Contributor III
Author

Thank you, looks like working.

ogautier62
Specialist II
Specialist II

ok thank's

good day