Get cumsum from aggregated field with Django orm

In my project, I want to get the sum of an "amount" field which comes form an aggregate. I've read some posts on this but I can't find a way to achieve what I want.

Example model:

class ScheduledOperation:
   day = models.dateField()
   amount = models.DecimalField(...)

Example queryset

{'day': datetime.date(2023, 2, 7), 'amount': Decimal('-500.00')} # same day each month
{'day': datetime.date(2023, 2, 7), 'amount': Decimal('1500.00')} # same day each month

{'day': datetime.date(2023, 3, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 3, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 4, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 4, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 5, 7), 'amount': Decimal('-500.00')}
{'day': datetime.date(2023, 5, 7), 'amount': Decimal('1500.00')}

{'day': datetime.date(2023, 5, 8), 'amount': Decimal('-4000.00')} # big op here

Where I am so far

ScheduledOperation.objects.order_by('day').values('day').annotate(day_tot=Sum('amount')) gives me the total amount for each day:

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000')}

What I want

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('1000')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('2000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('3000')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000'), 'cumul_amount':Decimal('4000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000'), 'cumul_amount':Decimal('0')}

What I tried

After reading other related posts on this subject, I've tried to use the Window function:

self.coming_scheduled_ops.order_by('day').values('day').annotate(
    day_tot=Sum('amount')
).annotate(
    cumul_amount=Window(
        Sum('amount'),order_by='day'
    )
)

but this does not work:

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('1500')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('3000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('4500')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('6000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000'), 'cumul_amount': Decimal('2000')}

I can't use Window(Sum('day_tot')), it throws django.core.exceptions.FieldError: Cannot compute Sum('day_tot'): 'day_tot' is an aggregate

Could someone help me understand the Window function plz?

I'm not sure if that's possible using Django ORM, but you can easily do this using Python.

queryset = ScheduledOperation.objects.all()
queryset = (
    queryset.order_by('day')
    .values('day')
    .annotate(day_tot=Sum('amount'))
)
cumul_amount = 0
for day in queryset:
    cumul_amount += day['day_tot']
    day['cumul_amount'] = cumul_amount

It results in:

{'day': datetime.date(2023, 2, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('1000')}
{'day': datetime.date(2023, 3, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('2000')}
{'day': datetime.date(2023, 4, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('3000')}
{'day': datetime.date(2023, 5, 7), 'day_tot': Decimal('1000'), 'cumul_amount': Decimal('4000')}
{'day': datetime.date(2023, 5, 8), 'day_tot': Decimal('-4000'), 'cumul_amount': Decimal('0')}
Back to Top