Django Running Balance / Cumulative Balance

user_12908935 0
Viewed 297

I have a transaction model with a function to calculate the balance(running balance/cumulative balance), but the balance output is returning as a Queryset.

<QuerySet [{'id': 1, 'cumsum': -9.0}, {'id': 2, 'cumsum': -16.0},{'id': 3, 'cumsum': -21.0}]>

how do I fix my function so that the balance only shows the numbers such that Oct 16 is -9.00, Oct 18 is -16.00, then Oct 20 is -21.00. Thank you in advance.

model.py

class Transaction(models.Model):
    transaction_date = models.DateField()
    account_nickname = models.ForeignKey(Accounts, on_delete=models.CASCADE)
    amount = models.FloatField()

    @property
    def balance(self):
        return Transaction.objects.annotate(cumsum=Func(Sum('amount'),template='%(expressions)s OVER (ORDER BY %(order_by)s)',order_by="id")).values('id', 'cumsum').order_by('id', 'cumsum')

views.py

def index(request):
    all_transactions = Transaction.objects.all()
    context = {
        "transactions": all_transactions
    }
    return render(request,'pages/index.html', context)

index.html

        {% for each_transaction in transactions %}
        <tr>
            <td>{{each_transaction.transaction_date}}</td>
            <td>{{each_transaction.account_nickname}}</td>
            <td>{{each_transaction.amount}}</td>
            <td>{{each_transaction.balance}}</td>
        </tr>
        {% endfor %}

Output: enter image description here

django-models django python
1 Answers

First remove your @property balance function

then in views.py

def index(request):
    all_transactions = Transaction.objects.annotate(cumsum=Func(Sum('amount'),template='%(expressions)s OVER (ORDER BY %(order_by)s)',order_by="id"))
    context = {
        "transactions": all_transactions
    }
    return render(request,'pages/index.html', context)

Then in your index.html

{% for each_transaction in transactions %}
<tr>
    <td>{{each_transaction.transaction_date}}</td>
    <td>{{each_transaction.account_nickname}}</td>
    <td>{{each_transaction.amount}}</td>
    <td>{{each_transaction.cumsum}}</td>
</tr>
{% endfor %}
user_122617520
Related