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?