topic Re: How sum with difirent dimensions in QlikView

How sum with difirent dimensions

irmantas — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How sum with difirent dimensions

Gysbert_Wassenaar — Mon, 02 Jan 2017 16:46:00 GMT

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

Re: How sum with difirent dimensions

irmantas — Mon, 02 Jan 2017 18:01:12 GMT

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

Re: How sum with difirent dimensions

vinieme12 — Mon, 02 Jan 2017 20:20:39 GMT

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

Re: How sum with difirent dimensions

sunny_talwar — Mon, 02 Jan 2017 22:44:57 GMT

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

Re: How sum with difirent dimensions

irmantas — Tue, 03 Jan 2017 08:13:10 GMT

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

Re: How sum with difirent dimensions

Anonymous — Tue, 03 Jan 2017 08:41:32 GMT

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

Re: How sum with difirent dimensions

sunny_talwar — Tue, 03 Jan 2017 12:10:43 GMT

Try this:

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

Re: How sum with difirent dimensions

irmantas — Wed, 11 Jan 2017 07:46:49 GMT

Thank you Sunny, it's works.