Django Group by month and payment mode, get a report in template only for the datas present

Question

In my database I have only data up to 'March' so I need only up to 'Mar'

I want achiever monthly sum grouped by 'mode'

def by_mode(request):
    all_modes = dict(Transactions.PAYMENT_MODE_CHOICES).keys()

    totals = Transactions.objects.annotate(
        month=TruncMonth('trans_date'),
        mode=F('trans_mode')
    ).values('month', 'mode').annotate(
        total=Sum('trans_amount')
    )

    data = {}
    for total in totals:
        month = total['month'].strftime('%B')
        mode = total['mode']
        if mode not in data:
            data[mode] = {}
        data[mode][month] = total['total']

    return render(request, 'by_mode.html', {'data': data, 'modes': all_modes})

class Transactions(models.Model): TRANSACTION_TYPE_CHOICES = ( ('income', 'Income'), ('expense', 'Expense'), )

PAYMENT_MODE_CHOICES = (
    ('cash', 'Cash'),
    ('enbd', 'ENBD'),
    ('nol', 'NOL'),
    ('sib', 'SIB'),
)

trans_id = models.AutoField(primary_key=True)
trans_date = models.DateField(verbose_name="Date")
trans_type = models.CharField(max_length=10, choices=TRANSACTION_TYPE_CHOICES, verbose_name="Type")
trans_details = models.ForeignKey(Details, on_delete=models.CASCADE, verbose_name="Details", related_name="trnsactions")
trans_category = models.ForeignKey(MasterCategory, on_delete=models.CASCADE, verbose_name="Category", related_name="transactions")
trans_mode = models.CharField(max_length=10, choices=PAYMENT_MODE_CHOICES, verbose_name="Payment Mode")
trans_amount = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="Amount")

objects = models.Manager()

class Meta:
    verbose_name_plural = "Transactions"

def __str__(self):
    return f"{self.trans_id} - {self.trans_type} - {self.trans_details} - {self.trans_category} - {self.trans_amount}"

Answer 1

You can do something like,

totals = Transactions.objects.annotate(
        month=TruncMonth('trans_date'),
    ).values('month').annotate(
        cash_t=Sum('trans_amount', filter=Q(trans_mode='cash')),
        enbd_t=Sum('trans_amount', filter=Q(trans_mode='enbd')),
        nol_t=Sum('trans_amount', filter=Q(trans_mode='nol')),
        sib_t=Sum('trans_amount', filter=Q(trans_mode='sib'))
    ).order_by()

OR

totals = Transaction.objects.annotate(month=TruncMonth('trans_date')).values(
   'month', 'trans_mode').annotate(tot=Sum('trans_amount')).order_by()

It is necessary to add order_by() at the end to prevent Django's default ordering. Refer to docs of order_by()