Grouping Sum by Month (1 Viewer)

JithuAccess

Member
Local time
Today, 06:58
Joined
Mar 3, 2020
Messages
297
Hello Guys,

I was trying to Group Payment Amount with Payment Date.

This is my Query:

1619709786233.png


and This is my Query Result:

1619709818731.png


As you could see, for July, the Total Payment was $221,260.00 and $22,126.00 and this is showing Separately in the Query. I think if there are different Dates, Query is grouping the Paid Amount by Dates. I want to show this Monthly. Like For July 2020 the Total Paid Amount $243,386.00.

Could you please let me know how to do this?

Thanks
 

Minty

AWF VIP
Local time
Today, 12:58
Joined
Jul 26, 2013
Messages
10,354
I'll add to Bob's excellent advice - add a year column and group on that well; otherwise, it will group all July's forever.

repYear: Year(NameOfDateField])
repMonth : Month([NameOfDateField])
 

bob fitz

AWF VIP
Local time
Today, 12:58
Joined
May 23, 2011
Messages
4,717
I'll add to Bob's excellent advice - add a year column and group on that well; otherwise, it will group all July's forever.

repYear: Year(NameOfDateField])
repMonth : Month([NameOfDateField])
Ooops. As usual, your excellent advice is even more excellent 🥴
 

Minty

AWF VIP
Local time
Today, 12:58
Joined
Jul 26, 2013
Messages
10,354
To be fair @bob fitz , I've fallen for that one on far too many occasions.

Hmm, those numbers look a bit big for January's sales...
 

JithuAccess

Member
Local time
Today, 06:58
Joined
Mar 3, 2020
Messages
297
Ooops. As usual, your excellent advice is even more excellent 🥴
Thanks a lot. It worked Perfectly.

Got a small issue. There isn't any Payment made for May-2020 so in the Query Result it is not showing. But we need to show this in our Report as May-2020 $0.00

Thanks
 

Minty

AWF VIP
Local time
Today, 12:58
Joined
Jul 26, 2013
Messages
10,354
That's actually quite awkward to overcome.

You can't group on data that doesn't exist (obviously), so you would need to bring something in that does have that date in it or create something that you can left join your results to, any table you have with guaranteed Calendar dates you need?

If you don't have one this can be where a Calendar Table is useful.
 

JithuAccess

Member
Local time
Today, 06:58
Joined
Mar 3, 2020
Messages
297
That's actually quite awkward to overcome.

You can't group on data that doesn't exist (obviously), so you would need to bring something in that does have that date in it or create something that you can left join your results to, any table you have with guaranteed Calendar dates you need?

If you don't have one this can be where a Calendar Table is useful.
Thanks a lot and Sorry for asking too much Stupid Questions.
 

plog

Banishment Pending
Local time
Today, 07:58
Joined
May 11, 2011
Messages
11,611
The same solution offered in this nearly identical thread you asked 2 days ago can be used for this:

 

Users who are viewing this thread

Top Bottom