filter by month inside views dictionary

Using Django Using the ORM
1 
def testing(request):
    totals_dict = {
        'Eating': Daily.objects.aggregate(eats=Sum('transamount', filter=Q(transcategory_id=5) & Q(transdate__month=1) | Q(transcategory_id=19) & Q(transdate__month=1) | Q(transcategory_id=20) & Q(transdate__month=1) | Q(transcategory_id=28) & Q(transdate__month=1) | Q(transcategory_id=31) & Q(transdate__month=1)))['eats'],
        'Trans_List': Daily.objects.all(),
        'Total_Bal': Daily.objects.all().aggregate(tb=Sum('transamount'))['tb'],
        'Cash_Bal': Daily.objects.aggregate(CashBAL=Sum('transamount', filter=Q(transmode__name='Cash')))['CashBAL'],
        'Enbd_Bal': Daily.objects.aggregate(ENBDBAL=Sum('transamount', filter=Q(transmode__name='ENBD')))['ENBDBAL'],
        'NoL_Bal': Daily.objects.aggregate(NoLBAL=Sum('transamount', filter=Q(transmode__name='NoL')))['NoLBAL'],
        'PayIT_Bal': Daily.objects.aggregate(PayITBAL=Sum('transamount', filter=Q(transmode__name='Pay IT')))[
            'PayITBAL'],
        'Sib_Bal': Daily.objects.aggregate(SibBAL=Sum('transamount', filter=Q(transmode__name='SIB')))['SibBAL'],
    }
    return render(request, 'testing.html', totals_dict)

In my above ‘view’, I am getting correct result for ‘Eating’ but I have hardcoded the month as ‘1’, so it’s 'Sum’ming only for January.

Please guide me to make every ‘Sum’ based on months.

Is it possible to create the dict values inside a loop like

"for i in 1 to 12 "
2

After a very long time, I am back here.
Please guide me.

3

So it’s not clear exactly what you’re looking for here. What do you want the final data structure to look like?

Are you looking for one value totals_dict['Eating'] that has the sum for all the entries transdate__month from 1 to 12?

Or are you looking for a list such that totals_dict['Eating'][0] is the sum for transdate__month=1 through totals_dict['Eating'][11] is the sum for transdate__month=12?

Or are you looking for some other data structure?

4

Looking for one value totals_dict['Eating'] that has the sum for all the entries transdate__month from 1 to 12?

This is what I am looking for. Report ( eating ) should be for each month as a sum.

 'Eating': Daily.objects.aggregate(eats=Sum('transamount', filter=Q(transcategory_id=5) & Q(transdate__month=1) | Q(transcategory_id=19) & Q(transdate__month=1) | Q(transcategory_id=20) & Q(transdate__month=1) | Q(transcategory_id=28) & Q(transdate__month=1) | Q(transcategory_id=31) & Q(transdate__month=1)))['eats'],

Here I have hard corded transdate__month as 1 (Jan)
I want to loop it for every month.

5 
exec("for i in range(12): print(Daily.objects.aggregate(alias=Sum('transamount', filter=Q(transdate__month=i+1) & Q(transmode__name='Cash'))))")
{'alias': Decimal('99.25')}
{'alias': Decimal('161.25')}
{'alias': Decimal('239')}
{'alias': None}
{'alias': None}
{'alias': None}
{'alias': None}
{'alias': None}
{'alias': None}
{'alias': None}
{'alias': None}
{'alias': None}

…
In python shell above is bringing the required results
but in views …
…

def bymonth(request):
	for x in range(12):
		print(Daily.objects.aggregate(mjc=Sum('transamount', filter=Q(transdate__month=[x+1]) & Q(transmode__name='Cash'))))['mjc']

totals_dict =  {
		'Total_Bal': Daily.objects.all().aggregate(tb=Sum('transamount'))['tb'],
        'Cash_Bal': Daily.objects.aggregate(CashBAL=Sum('transamount', filter=Q(transmode__name='Cash')))['CashBAL'],
        'Enbd_Bal': Daily.objects.aggregate(ENBDBAL=Sum('transamount', filter=Q(transmode__name='ENBD')))['ENBDBAL'],
        'NoL_Bal': Daily.objects.aggregate(NoLBAL=Sum('transamount', filter=Q(transmode__name='NoL')))['NoLBAL'],
        'PayIT_Bal': Daily.objects.aggregate(PayITBAL=Sum('transamount', filter=Q(transmode__name='Pay IT')))['PayITBAL'],
        'Sib_Bal': Daily.objects.aggregate(SibBAL=Sum('transamount', filter=Q(transmode__name='SIB')))['SibBAL'],
        'monthsd': monjc
		}
	return render(request, 'bymonth.html', totals_dict)

While running the above , I am getting the below error

Request Method: GET
Request URL: http://127.0.0.1:8000/bymonth
Django Version: 4.1.7
Exception Type: TypeError
Exception Value: Field ‘None’ expected a number but got [1].
6

[quote=“KenWhitesell, post:3, topic:13749”]
are you looking for a list such that totals_dict['Eating'][0] is the sum for

 `transdate__month=1` through `totals_dict['Eating'][11]` is the sum for `transdate__month=12`?

Exactly as above.

7

Ok, first I’m going to simplify this:

To this:

'Eating': Daily.objects.aggregate(
    eats=Sum('transamount', 
            filter=Q(transcategory_id__in=[5, 19, 20, 28, 31], transdate__month=1)
    )['eats'],

Now, we’ll wrap that in a list comprehension to iterate over the months:

'Eating': [
    Daily.objects.aggregate(eats=Sum('transamount', 
      filter=Q(transcategory_id__in=[5, 19, 20, 28, 31], transdate__month=month)
    )['eats']
    for month in range(1,13)
  ],
8

wow! that’s great.
I will apply this and check.
Thanks a lot.

9 
def bymonth(request):
    totals_dict = {
        'Total_Bal': Daily.objects.all().aggregate(tb=Sum('transamount'))['tb'],
        'Cash_Bal': Daily.objects.aggregate(CashBAL=Sum('transamount', filter=Q(transmode__name='Cash')))['CashBAL'],
        'Enbd_Bal': Daily.objects.aggregate(ENBDBAL=Sum('transamount', filter=Q(transmode__name='ENBD')))['ENBDBAL'],
        'NoL_Bal': Daily.objects.aggregate(NoLBAL=Sum('transamount', filter=Q(transmode__name='NoL')))['NoLBAL'],
        'PayIT_Bal': Daily.objects.aggregate(PayITBAL=Sum('transamount', filter=Q(transmode__name='Pay IT')))[
            'PayITBAL'],
        'Sib_Bal': Daily.objects.aggregate(SibBAL=Sum('transamount', filter=Q(transmode__name='SIB')))['SibBAL'],
        'Eating': [Daily.objects.aggregate(
            eats=Sum('transamount', filter=Q(transcategory_id__in=[5, 19, 20, 28, 31], transdate__month=month))['eats']
            for month in range(1, 13))
                   ],
    }
    return render(request, 'bymonth.html', totals_dict)

My bymonth.html

{% extends 'base.html' %}
{% block content %}
<br><br><br>
<h1>Testing Place</h1>
<style style="accent-color:red;">
table, tr, td, th {
  border:2px solid blueviolet;
}
</style>
<div style="accent-color:blueviolet ;" >
	Total Balance : {{ Total_Bal|floatformat:'2g' }}<br>
    Cash Balance : {{ Cash_Bal |floatformat:'2g'}}<br>
    Enbd Balance : {{ Enbd_Bal |floatformat:'2g'}} <br>
    NoL Balance : {{ NoL_Bal|floatformat:'2g' }} <br>
    Pay IT Balance : {{ PayIT_Bal|floatformat:'2g' }} <br>
    Sib Balance : {{ Sib_Bal|floatformat:'2g'}} <br>
    Eating : {{ Eating }} <br>
</div>

But I am getting error
…
File “D:\Learn\Expenses\dailytrans\views.py”, line 41
eats=Sum(‘transamount’, filter=Q(transcategory_id__in=[5, 19, 20, 28, 31], transdate__month=month))[‘eats’]
^^^^^
SyntaxError: invalid syntax. Maybe you meant ‘==’ or ‘:=’ instead of ‘=’?
…

10

Missing paren - there should be one more paren (three in total) after the transdate__month=month clause.

And you’ve got an extra paren after the for month in range clause.

11

Thanks, That started working now. Need some more improvement.

   totals_dict = {
        'Total_Bal': [Daily.objects.all().aggregate(tb=Sum('transamount', filter=Q(transdate__month=month)))['tb']
                      for month in range(1, 13)
                      ],
        'Cash_Bal': [Daily.objects.aggregate(
            CashBAL=Sum('transamount', filter=Q(transmode__name='Cash', transdate__month=month)))['CashBAL']
                     for month in range(1, 13)
                     ],
        'Enbd_Bal': [Daily.objects.aggregate(
            ENBDBAL=Sum('transamount', filter=Q(transmode__name='ENBD', transdate__month=month)))['ENBDBAL']
                     for month in range(1, 13)
                    ],
        'Eating': [Daily.objects.aggregate(
            eats=Sum('transamount', filter=Q(transcategory_id__in=[5, 19, 20, 28, 31], transdate__month=month)))['eats']
                   for month in range(1, 13)
                   ],
    }
    return render(request, 'alltrans.html', totals_dict)

and my HTML is

{% extends 'base.html' %}
{% block content %}
<br><br><br>
<h1>Testing Place</h1>
<style style="accent-color:red;">
table, tr, td, th {
  border:2px solid blueviolet;
}
</style>
<div style="accent-color:blueviolet ;" >
	Total Balance : {{ Total_Bal }}<br>
    Cash Balance : {{ Cash_Bal }}<br>
    Enbd Balance : {{ Enbd_Bal }} <br>
    Eating : {{ Eating }} <br>
</div>
{% endblock %}

And my result is

Total Balance : [Decimal('2630.36000000000'), Decimal('1575.66000000000'), Decimal('120.75'), None, None, None, None, None, None, None, None, None]
Cash Balance : [Decimal('99.25'), Decimal('161.25'), Decimal('200'), None, None, None, None, None, None, None, None, None]
Enbd Balance : [Decimal('2215.72000000000'), Decimal('1361.66000000000'), Decimal('-59.25'), None, None, None, None, None, None, None, None, None]
Eating : [Decimal('-1623.06000000000'), Decimal('-2410.03000000000'), Decimal('-11.25'), None, None, None, None, None, None, None, None, None]

I want to avoid “None”.
Only the months which is having data should be in the result.

1 Like
12

Actually, no, that would be a mistake. If you remove the None results, how can you tell which months you have?
What you probably want to do is either replace that none with a different value such as a blank string or a value of zero, or possibly handle that situation in your template.

13

Agreed. Will play in template.

14

image
image872×156 3.49 KB

How to bring the values in the template by month.

<table border="2">
  <tr>
	<td></td>
	<td>Jan</td>
	<td>Feb</td>
	<td>Mar</td>
	<td>Apr</td>
	<td>May</td>
	<td>Jun</td>
	<td>Jul</td>
	<td>Aug</td>
	<td>Sep</td>
	<td>Oct</td>
	<td>Nov</td>
	<td>Dec</td>
  </tr>
  <tr>
	<td>Cash</td>
	<td>{{ Cash_Bal }}</td>
	<td>feb cash</td>
  </tr>
  <tr>
	<td>ENBD</td>
	<td>{{ Enbd_Bal }}</td>
	<td>feb bd</td>
  </tr>
  <tr>
	<td>NoL</td>
  </tr>
  <tr>
	<td>Pay IT</td>
  </tr>
  <tr>
	<td>SIB</td>

My template is as above

15

See Built-in template tags and filters | Django documentation | Django

16 
def alltrans(request):
    x = len(Daily.objects.values('transdate__month').distinct())+1
    totals_dict = dict(title='Alltrans', Total_Bal=[
        Daily.objects.all().aggregate(tb=Sum('transamount', filter=Q(transdate__month=month)))['tb']
        for month in range(1, x)
        ], Cash_Bal=[
        Daily.objects.aggregate(CashBAL=Sum('transamount', filter=Q(transmode__name='Cash', transdate__month=month)))[
            'CashBAL']
        for month in range(1, x)
        ], Enbd_Bal=[
        Daily.objects.aggregate(ENBDBAL=Sum('transamount', filter=Q(transmode__name='ENBD', transdate__month=month)))[
            'ENBDBAL']
        for month in range(1, x)
        ], NoL_Bal=[
        Daily.objects.aggregate(NoLBAL=Sum('transamount', filter=Q(transmode__name='NoL', transdate__month=month)))[
            'NoLBAL']
        for month in range(1, x)
        ], PayIT_Bal=[
        Daily.objects.aggregate(PayITBAL=Sum('transamount', filter=Q(transmode__name='PayIT', transdate__month=month)))[
            'PayITBAL']
        for month in range(1, x)
        ], Sib_Bal=[
        Daily.objects.aggregate(SibBAL=Sum('transamount', filter=Q(transmode__name='NoL', transdate__month=month)))[
            'SibBAL']
        for month in range(1, x)
        ], Eating=[Daily.objects.aggregate(
        eats=Sum('transamount', filter=Q(transcategory_id__in=[5, 19, 20, 28, 31], transdate__month=month)))['eats']
                   for month in range(1, x)
                   ])
    return render(request, 'alltrans.html', totals_dict)

I have learned to alter my required result.

And my template is

<!DOCTYPE html>
<html lang="en">
 <head>
  <meta charset="UTF-8">
	<style style="accent-color:red;">
		table, tr, td, th {
  		border:2px solid blueviolet;
		}
	</style>
 </head>
 <body>
{% extends 'base.html' %}
{% block content %}
<br><br><br><br>
<table border="2">
  <tr>
	<td></td>
	<td>January</td>
	<td>February</td>
	<td>March</td>
	<td>April</td>
	<td>May</td>
	<td>June</td>
	<td>July</td>
	<td>August</td>
	<td>September</td>
	<td>October</td>
	<td>November</td>
	<td>December</td>
  </tr>
<tr>
	<td>Cash</td>
	{% for cash in Cash_Bal %}
		<td>{{ cash | floatformat:'2g' }}</td>
	{% endfor %}
</tr>
<tr>
	<td>ENBD</td>
	{% for enbd in Enbd_Bal %}
		<td>{{enbd | floatformat:'2g'}}</td>
	{% endfor %}
</tr>
<tr>
	<td>NoL</td>
	{% for nol in NoL_Bal %}
		<td>{{ nol | floatformat:'2g'}}</td>
	{% endfor %}
</tr>
<tr>
	<td>Pay IT</td>
	{% for payit in PayIT_Bal %}
		<td>{{ payit | floatformat:'2g'}}</td>
	{% endfor %}
</tr>
<tr>
	<td>SIB</td>
	{% for sib in Sib_Bal %}
		<td>{{ sib | floatformat:'2g'}}</td>
	{% endfor %}
</tr>
</table>

{% endblock %}
 </body>
</html>

Any my output is
image

Can I still make improvements in this …
Please suggest.

17

I’m not sure what all improvements you’d be looking for.

You might want to start looking at some CSS to make all the columns the same width.

18

Thanks for your continuous support.

I was thinking about

totals_dict = dict(title='Alltrans', Total_Bal=[
        Daily.objects.all().aggregate(tb=Sum('transamount', filter=Q(transdate__month=month)))['tb']
        for month in range(1, x)
        ], Cash_Bal=[
        Daily.objects.aggregate(CashBAL=Sum('transamount', filter=Q(transmode__name='Cash', transdate__month=month)))[
            'CashBAL']
        for month in range(1, x)
        ], Enbd_Bal=[
        Daily.objects.aggregate(ENBDBAL=Sum('transamount', filter=Q(transmode__name='ENBD', transdate__month=month)))[
            'ENBDBAL']
        for month in range(1, x)
        ], NoL_Bal=[
        Daily.objects.aggregate(NoLBAL=Sum('transamount', filter=Q(transmode__name='NoL', transdate__month=month)))[
            'NoLBAL']
        for month in range(1, x)
        ], PayIT_Bal=[
        Daily.objects.aggregate(PayITBAL=Sum('transamount', filter=Q(transmode__name='PayIT', transdate__month=month)))[
            'PayITBAL']
        for month in range(1, x)
        ], Sib_Bal=[
        Daily.objects.aggregate(SibBAL=Sum('transamount', filter=Q(transmode__name='NoL', transdate__month=month)))[
            'SibBAL']
        for month in range(1, x)
        ]

the “for” loop is repeating the same. Can it be replaced by a single for loop.

19

Actually, using aggregates and being willing to restructure how you’re collecting the results in your view, this can probably be done without any explicit loops at all.

20

Please show me some light on that.