Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this:
=If(WildMatch(ITEM_ID, 'PA-6*'), Sum(TOTAL <BOM_id> QTY*Price), Sum(Price))
Try this expression: sum({<BOM_id={'PA-60*'}>}QTY*Price)
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))
I think you are trying it in a Textbox? even so you only need what gwassenaar posted, remove the if()condition
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))
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
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!!!
Try this:
=If(WildMatch(ITEM_ID, 'PA-6*'), Sum(TOTAL <BOM_id> QTY*Price), Sum(Price))
Thank you Sunny, it's works.