Grouping Sum by Month

JithuAccess

Member
Local time
Yesterday, 19:02
Joined
Mar 3, 2020
Messages
325
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
 
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])
 
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 🥴
 
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...
 
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
 
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.
 
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.
 
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

Back
Top Bottom