How Can I Sum Django Models Field by Dates and Display Totals base on Current Month and Year

user_15239717 0
Viewed 699

I am a novice in python Django framework and I wish someone should please help on some piece of code I have been struggling with hours. I have an Expenditure Model with date and amount Fields among others, and I want to calculate total amount for the current month and display in django template through the views using the context dictionary. Here is my Expenditure model code:

class Expenditure(models.Model):
      amount = models.PositiveIntegerField(null=False)
      date = models.DateField(auto_now_add=False, auto_now=False, null=False)

Views.py codes here:

from django.db.models import Count, Sum
from django.db import connection

def index(request):
    truncate_month = connection.ops.date_trunc_sql('month','day')
    total_income = Income.objects.extra({'month': truncate_month}).values('date').annotate(Sum('amount'))
    context = {
         'total_income':total_income,
    }
    return render(request, 'dashboard/index.html', context)

This is how I am trying to display the Sum Total of amount for the current month and year in Django template.

{% for income in total_income %}
   {{income.date}}{{income.amount}}
{% endfor %}

The result of my code above is displaying the months of dates from the Expenditure Model on the template whereas this is the only Output Result I am expecting: 'Aug. 2021: 13,000' Only Because August is the only current Month in 2021. Your kind assistance would help a lot. Thanks

django-queryset django-templates django python
1 Answers

It seem you just didn't use the truncated fields in your values call.

In line with that, you can use django's TruncMonth to annotate and group by the month:

from django.db.models.functions import TruncMonth


total_income = Income.objects.annotate(month=TruncMonth('date')).values('month').annotate(total_amount=Sum('amount'))

context = {
     'total_income': total_income,
}

And then in your template, use those annotated fields like this:

{% for income in total_income %}
  {{ income.month }} {{ income.total_amount }}
{% endfor %}

EDIT:

If you just need it on the current month and year, just filter on that and run an aggregate like:

import datetime


now = datetime.datetime.now()
total_income = Income.objects.filter(
    date__year=now.year, date__month=now.month
).aggregate(total_income=Sum('total_income'))['total_income']

context = {
    'year': now.year,
    'month': now.strftime('%B'),
    'total_income': total_income,
}

In the template, no need to iterate on anything:

  {{ year }} - {{ month }} : {{ total_income }}
user_67598440
Related