Finding sum of items with the same order number using grouping/set analysis

[QlikUser12321]

SrNo	Order No	Quantity	Price per Item
1	A	5	10
2	A	3	25
3	A	6	11
4	A	1	17
5	B	3	30
6	B	2	15

Hello, suppose I have a table and I want to find the total sum of items with respective to the orders, how can I achieve that? I want the output to be as below:

SrNo	Order No	Quantity	Price per item	Total Price	Price Per Order
1	A	5	10	50	208
2	A	3	25	75	208
3	A	6	11	66	208
4	A	1	17	17	208
5	B	3	30	90	120
6	B	2	15	30	120

Basically Total Price is just Qty * Price per item, and price per order is the sum of price per item which have the same order number (i.e. grouping by order number).

Is there a way to do this in the front end? If not, any other suggestions? Any help will be greatly appreciated. 

P.S. Sorry if this is super simple, I am new to Qlik and want to learn more 🙂

[chaorenzhu · Accepted solution]

On frond end, you can group by [Order No] and perform inner join, something like:

Table:

load * from XXX;

inner join

load [Order No],sum(Quantity*[Price per Item]) as [Price Per Order]

resident Table group by [Order No];

If you prefer doing this on the backend, just use aggr()

aggr(sum(Quantity*[Price per Item]),[Order No])

[sidhiq91]

@chaorenzhu Using front end I am getting the Output as per the expectation. But when using the above expression in the back end I am getting the output as below. But how do we replace the null values? please find attached.

[chaorenzhu · Accepted solution]

Using the expression as a dimension will produce the result.

If using as a measure, you need

aggr(nodistinct sum(Quantity*[Price per Item]),[Order No])

[sidhiq91]

@chaorenzhu  Awesome! 

[QlikUser12321]

Thank you so much @chaorenzhu , your solution has worked here. Thanks @sidhiq91  for trying the solution out for me.

Really appreciate this community and your help guys, cheers!