How sum with difirent dimensions

[irmantas]

Hello,

I am new at Qlik, and can't creat Expression for my appl.

I have this data:

BOM_id	Item_id	QTY	Price
PA-0001-001	PA-12001	1	0,5
PA-0001-001	PA-12006	2	1,5
PA-0001-001	PA-60001	1	??
PA-0001-001	PA-35001	1	3,5
PA-0001-002	PA-12001	2	4,5
PA-0001-002	PA-12006	1	5,5
PA-0001-002	PA-60001	1	??
PA-0001-002	PA-35001	1	0,5
PA-60001-001	PA-4447	2	1,5
PA-60001-001	PA-4544	0,5	2,5
PA-60001-001	PA-8545	2	3,5
PA-60001-002	PA-4447	1	4,5
PA-60001-002	PA-4544	0,3	0,5
PA-60001-002	PA-8545	2	0,8

I need sum total price per BOM_id and Item_id, but were Item_id begins with PA-60*, I need sum all BOM_id, begins PA-60* and ends with father BOM_id 3 digits end.

Exempl:

(BOM_id) PA-0001-001   (Item_id) PA-60001 to find price, must sum all  (BOM_id) PA-60001-001 (QTY*Price)

I hope it is not too complicated explanation.

[Gysbert_Wassenaar]

Try this expression: sum({<BOM_id={'PA-60*'}>}QTY*Price)

[irmantas]

Thankyou Gysbert,

But something wrong. Do not works. Full line of expression is:

=if(wildmatch(ITEM_ID, '*PA-6*'),sum({<BOM_ID={'PA-6*'}>}QTY*Price),Sum (Price))

[vinieme12]

I think you are trying it in a Textbox? even so you only need what gwassenaar‌‌ posted, remove the if()condition

[sunny_talwar]

So for PA-0001-001 you would like to see like this

1*0.5 + 2*1.5 = 3.5?

If this is right, then may be you need this:

=If(WildMatch(ITEM_ID, '*PA-6*'), Sum(TOTAL <BOM_id> {<BOM_ID={'PA-6*'}>} QTY*Price), Sum(Price))

[irmantas]

That is right, PA-0001-001 I would like to see like this:

1*0.5+2*1.5+1*??+1*3.5=....

Were '??' must be a sum of all PA-60001-001 like this: 2*1,5+0.5*2.5+2*3.5=11.25

So PA-0001-001 like to see like this: 1*0.5+2*1.5+1*11.25+1*3.5=18.25

Maybe now more clearly explained

[Anonymous]

Hi irmantas,

Try this expression:

RangeSum(

    Sum(Price),

    SUM(TOTAL <BOM_id> {<BOM_id=P({<Item_id={'PA-60*'}>}BOM_id)>} QTY*Price)

)

As price are chars on PA-60* you can sum all price for all records.

On the second part of expresion, you can sum total of each BOM_id, where the BOM_id have one item like PA-60*

Regards!!!

[sunny_talwar · Accepted solution]

Try this:

=If(WildMatch(ITEM_ID, 'PA-6*'), Sum(TOTAL <BOM_id> QTY*Price), Sum(Price))

[irmantas]

Thank you Sunny, it's works.