filter by month inside views dictionary

itsjamaal · May 17, 2022, 6:38am

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 "

adiraijamal · March 2, 2023, 4:35am

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

KenWhitesell · March 2, 2023, 1:07pm

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?

adiraijamal · March 3, 2023, 4:09am

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.

adiraijamal · March 3, 2023, 5:00am

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].

adiraijamal · March 3, 2023, 6:10am

[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.

KenWhitesell · March 3, 2023, 12:28pm

Ok, first I’m going to simplify this:

itsjamaal:

'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'],

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)
  ],

adiraijamal · March 6, 2023, 4:10am

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

adiraijamal · March 6, 2023, 4:49am

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 ‘=’?
…

KenWhitesell · March 6, 2023, 11:39am

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.

adiraijamal · March 7, 2023, 4:39am

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.

KenWhitesell · March 7, 2023, 4:59am

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.

adiraijamal · March 7, 2023, 5:11am

Agreed. Will play in template.

adiraijamal · March 7, 2023, 5:14am

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

KenWhitesell · March 7, 2023, 1:39pm

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

adiraijamal · March 9, 2023, 6:14am

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

Can I still make improvements in this …
Please suggest.

KenWhitesell · March 9, 2023, 12:21pm

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.

adiraijamal · March 9, 2023, 12:25pm

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.

KenWhitesell · March 9, 2023, 12:35pm

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.

adiraijamal · March 9, 2023, 12:41pm

Please show me some light on that.

Topic		Replies	Views
Group by month and sum it in views Using the ORM	3	3530	May 28, 2022
add values inside for loop Forms & APIs	29	1910	May 1, 2022
Django Group by month and payment mode, get a report in template only for the datas present Templates & Frontend	1	392	March 28, 2023
How to make a monthly comparison of two queryset Using the ORM	2	313	January 13, 2024
Django Add field values as per month and year wise and consolidate Using the ORM	7	1347	March 13, 2024

filter by month inside views dictionary

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

While running the above , I am getting the below error

Related topics