[Tutorial] Jinja How To : Group data and calculate sum

Frappe Framework Print Formats
1

You can use below code to group data and calculate sum in Jinja Script:

{% 
set items = [
{'item_code':'item1', 'qty': 100, 'amount': 1000},
{'item_code':'item2', 'qty': 200, 'amount': 2000},
{'item_code':'item1', 'qty': 100, 'amount': 1000},
{'item_code':'item3', 'qty': 300, 'amount': 3000},
{'item_code':'item2', 'qty': 200, 'amount': 2000}
]

%}
<table>
<thead>
	<tr>
		<th>Item Code</th>
		<th>Qty</th>
		<th>Total</th>
	</tr>
</thead>
<tbody>
<!-- group items by item_code //-->
{% for item, item_group in items|groupby('item_code') %}
	<tr>
		<td>{{item}}</td>
		<!-- get group total //-->
		<td>{{ item_group|sum(attribute='qty') }}</td>
		<td>{{item_group|sum(attribute='amount')}}</td>
	</tr>

{%- endfor -%}
</tbody>

<tfoot>
	<tr>
		<th>Grand Total</th>
		<!-- get grand total //-->
		<th>{{items|sum(attribute='qty')}}</th>
		<th>{{items|sum(attribute='amount')}}</th>
	</tr>
</tfoot>
</table>

Output:

Item Code Qty Total
item1 200 2000
item2 400 4000
item3 300 3000
Grand Total 900 9000
10 Likes
2

Awesome :smiley:

3

Hello,

Do you know how can {{ items|sum(attribute='amount') }} be formatted to print as currency? I’ve made some tests with get_formatted but with no success.

4

Set value in a variable then do format variable value.

1 Like
5

58%20PM
I get this error please help :slight_smile:

6

You may try something like

<td class="rt">{{ "USD {:,.2f}".format(doc.YOUR_FIELD) }}</td>
1 Like
7

this is what I used

{{ frappe.utils.fmt_money(doc.items|sum(attribute= ‘amount’)) }}

assuming your child table is items.

1 Like
9

Can someone help me with this?

10 
 {{ frappe.format(opening_balance, {'fieldtype': 'Currency'}) }}
11

Hello @sanjay , Thanks for the tutorial. Can you guide on how to sum this with custom field/column in the child table like SIZE/COLOR:

https://discuss.frappe.io/t/group-data-and-calculate-sum-by-custom-fields-in-item-table/132635