Sum total group by

[Keyuser]

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

[sunny_talwar]

Try this

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

[zhadrakas]

dimensions: Order number, pieces

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

that should work for you

regards

tim

[sunny_talwar]

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.

[Keyuser]

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

[zhadrakas]

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

[Keyuser]

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.

[zhadrakas]

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;

[cmano]

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.