Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Keyuser
Contributor
Contributor

Sum total group by

Hy,

I have a table with the following structure:

Order numberpieces
12345672
12345674
12345673
98765435
98765433
98765432

Am looking for a formula for the following result:

Sum of pieces / total sum pieces
goup by order number

For example:

Order numberpieces%
1234567222,22%
1234567444,44%
1234567333,33%
   
9876543550,00%
9876543330,00%
9876543220,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!!

8 Replies
sunny_talwar

Try this

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

zhadrakas
Specialist II
Specialist II

dimensions: Order number, pieces

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

that should work for you

sample.png

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
Contributor
Contributor
Author

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

zhadrakas
Specialist II
Specialist II

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
Contributor
Contributor
Author

Perfekt, it's working.

 

But now  I need the function directly in the script

Invoice numberOrder numberPos.pieces%
test0112345671222,22%
test0112345672444,44%
test0112345673333,33%
     
test0112345671550,00%
test0112345672330,00%
test0112345673220,00%

 

How to group by this?

Thanks.

zhadrakas
Specialist II
Specialist II

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

Hello my friend.

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

I have the below script.

Screenshot 2021-11-06 130903.png

 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.