topic Re: Sum total group by in QlikView

Sum total group by

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

Hy,

I have a table with the following structure:

Order number	pieces
1234567	2
1234567	4
1234567	3
9876543	5
9876543	3
9876543	2

Am looking for a formula for the following result:

Sum of pieces / total sum pieces
goup by order number

For example:

Order number	pieces	%
1234567	2	22,22%
1234567	4	44,44%
1234567	3	33,33%

9876543	5	50,00%
9876543	3	30,00%
9876543	2	20,00%

Tried it with sum(total but of course that only works for a selection of a single order number.

Do you have any ideas for me?

Thank you!!

Re: Sum total group by

sunny_talwar — Tue, 30 Apr 2019 13:12:58 GMT

Try this

Sum(pieces)/Sum(TOTAL <[Order number]> pieces)

Re: Sum total group by

zhadrakas — Tue, 30 Apr 2019 13:18:47 GMT

dimensions: Order number, pieces

Expression: sum(TOTAL <[Order number], pieces>pieces) / sum(TOTAL <[Order number]>pieces)

that should work for you

regards

tim

Re: Sum total group by

sunny_talwar — Tue, 30 Apr 2019 13:23:13 GMT

I don't think you need TOTAL <[Order number], pieces> in the numerator. Expression should work the same way without it, because you are totaling it using the same dimensions you have in the chart.

Re: Sum total group by

Keyuser — Thu, 02 May 2019 09:58:12 GMT

Thanks for the input.
But pieces are determined as a formula. (Pieces sold - pieces ordered)
How do I get this in the formula?

Re: Sum total group by

zhadrakas — Thu, 02 May 2019 10:37:13 GMT

Sum(Pieces_sold - pieces_ordered)/Sum(TOTAL <[Order number]> Pieces_sold - pieces_ordered)

alternatively you could calculate that field in script like
Pieces_sold - pieces_ordered as pieces

then you coul use the Expression mentioned above

Re: Sum total group by

Keyuser — Wed, 08 May 2019 13:14:26 GMT

Perfekt, it's working.

But now I need the function directly in the script.

Invoice number	Order number	Pos.	pieces	%
test01	1234567	1	2	22,22%
test01	1234567	2	4	44,44%
test01	1234567	3	3	33,33%

test01	1234567	1	5	50,00%
test01	1234567	2	3	30,00%
test01	1234567	3	2	20,00%

How to group by this?

Thanks.

Re: Sum total group by

zhadrakas — Thu, 09 May 2019 08:29:46 GMT

here you go:

//replace this "Inline-Statement" with your Load script
TEST_TMP:
LOAD * INLINE [
Invoice_number, Order_number, Pos, pieces
test01, 12345, 1, 2
test01, 12345, 2, 4
test01, 12345, 3, 3
test02, 23456, 1, 5
test02, 23456, 2, 3
test02, 23456, 3, 2
];

//sort to make group by faster
TEST_SORT:
NoConcatenate
Load *
Resident TEST_TMP
order by Invoice_number, Order_number;

drop table TEST_TMP;

// add a TOTAL Column
left join
Load Invoice_number,
Order_number,
sum(pieces) as pieces_TOTAL
Resident TEST_SORT
Group by Invoice_number, Order_number;

TEST_FINAL:
NoConcatenate
Load *,
pieces / pieces_TOTAL as pieces_%
Resident TEST_SORT
;
drop table TEST_SORT;

Re: Sum total group by

cmano — Sat, 06 Nov 2021 11:14:52 GMT

Hello my friend.

I have a similar problem and if you could help i would appreciate it

I have the below script.

What "fuction" i need to put in script in order to create a total sum of Scrap, accepted & reworking.

Total=sum(Scrap, accepted & reworking).

Thanks in advance.