Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can use:
SUM(total {1<TransType={Prod}>}Qty)
Or
SUM(total {<TransType={Prod},ItemId>}Qty)
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.
Hi,
as dimensions :
ItelId, TrasRefId
expressions :
=sum({<TransType={'Prod Line'} >} Qty)
= sum(aggr(nodistinct sum( {<TransType = {Prod} >} Qty),TrasRefId))
regards
Thanks for answer, but both formulas calculating all of Prod Qty, but I need only these Prod with was used selected ItemId.
it's ok for me
(I add an item)
it was not used in calcultion for item003
Working:) But if I select one dimension PA-003 , Prod =0
add {1 :
= sum(aggr(nodistinct sum( {1<TransType = {Prod} >} Qty),TrasRefId))
Thank you, looks like working.
ok thank's
good day