[SOLVED] Select TruncYear start with specific year

Select TruncYear start with specific year

I have Transactions stored in a Table, to select the last 3 years I wrote a simple Query in Django. I did this mid last year and it seemed to be fine. Now it would be nice if it would return me the year 2025 with 0, how could I achive that?

Current Query:

Transactions.objects
.annotate(year=TruncYear('timestamp'))
.values('year')
.order_by('-year')
.annotate(total=Sum('amount'))
.values('year', 'total')[:3]

This returns me the data for the years 2024,2023,2022 which is okay, but it would look more nice if it would return the data for 2025,2024,2023

Something like a change to get the current year and browse the table from there on. regardless of the table transactions having data for this year or not.

Solution

To do this you could modify your query to include the current year and then use a combination of annotate, Sum, and Coalesce to ensure it provides totals for a given year that are missing and defaults them to 0. You could then dynamically build a set of expected years and combine those into your query set results.

I suppose this will work for you:

from django.db.models import Sum, Value
from django.db.models.functions import Coalesce, TruncYear
from datetime import datetime

current_year = datetime.now().year

years_to_include = [current_year, current_year - 1, current_year - 2]

results = (
    Transactions.objects
    .annotate(year=TruncYear('timestamp'))
    .values('year')
    .order_by('-year')
    .annotate(total=Coalesce(Sum('amount'), Value(0)))  # Default missing totals to 0
    .values('year', 'total')
)

results_dict = {result['year'].year: result['total'] for result in results}

final_results = [
    {'year': year, 'total': results_dict.get(year, 0)}
    for year in years_to_include
]

print(final_results)