Django: Query Group by Month with filling empty months

Viewed 109

Trying to group by sales by month like below which works perfect

monthbased = any_queryset.annotate(sales_month=TruncMonth('BidDate')
    ).values('sales_month'
    ).annotate(sum=Sum(F('Quantity')*F('SellPrice'))
    )

My question is about the months with no sales. I am using this result in a bar chart and if any month does not have sales then those months will not appear in the chart I want to show empty months with zero values.

I did it like this

# convert queryset to list of dicts
monthbased_list = [{'sales_month': x['sales_month'], 'sum': x['sum'], 'dcount': x['dcount'], 'profit': x['profit']} for x in monthbased]
# fill empty year and months until utc now
now = timezone.now()
for y in range(now.year-2, now.year+1):
    for m in range(1, 13):
        if not any(d['sales_month'] == timezone.make_aware(datetime(y, m, 1)) for d in monthbased_list):
            monthbased_list.append({'sales_month': timezone.make_aware(datetime(y, m, 1)), 'sum': 0, 'dcount': 0, 'profit': 0})
# sort by year and month
monthbased_list = sorted(monthbased_list, key=lambda k: k['sales_month'])
# delete future months
monthbased_list = [x for x in monthbased_list if x['sales_month'] <= now]

Is there a better way to do it?

0 Answers
Related