Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Willie Mullen

# SOQL - New Deals This Year By Month

Hi, I'm struggling with a SOQL I'm working on. I'm trying to show a chart of # of Won Deals this Fiscal Year grouped by Close Month. Is this possible?
Best Answer chosen by Willie Mullen
Frédéric Trébuchet
What if you change "SUM(of_New_Clients__c), CALENDAR_MONTH(CloseDate)" by "CALENDAR_MONTH(CloseDate), SUM(of_New_Clients__c)"? Not sure but month 25 looks strange!
Sorry, I don't know anything (yet) about reports.
May some help here http://www.salesforce.com/us/developer/docs/workbook_analytics/workbook_analytics.pdf.

Regards,
Fred

Frédéric Trébuchet
Hi,

This should works if fields names are good (not sure with that):
```AggregateResult[] results = [SELECT Sum(Amount)
FROM Opportunity
WHERE IsWon = true
AND FISCAL_YEAR(CloseDate) = the_fiscal_year_you_want,
GROUP BY CALENDAR_MONTH(CloseDate);
```
Hope this helps,
Fred
BalajiRanganathan

this SOQL should work

SELECT count(Id) wondeals FROM Opportunity where closedate = THIS_YEAR and IsWon = true group by CALENDAR_MONTH(closedate)
Frédéric Trébuchet
I think "FISCAL_YEAR(CloseDate) = the_fiscal_year_you_want" is better than "closedate = THIS_YEAR" but "count(Id)" is better than "Sum(Amount)" for your question.
So you have to mix the 2 propositions to have the best one (I think so).

Regards,
Fred
Willie Mullen
So here is what I have now (based on these two responses). Still not working properly I'm sure there's an error somewhere.

AggregateResult[] results = [SELECT count(ID)
FROM Opportunity
WHERE IsWon = true
AND FISCAL_YEAR(CloseDate) = THIS_YEAR,
GROUP BY CALENDAR_MONTH(CloseDate)

Sorry, I'm very new to SOQL.
Frédéric Trébuchet
This works
```SELECT count(id), FISCAL_YEAR(CloseDate) fy
FROM Opportunity
WHERE IsWon = true
AND CloseDate = THIS_YEAR
GROUP BY FISCAL_YEAR(CloseDate)```
Fred

Willie Mullen
Here's where I am now:
Select SUM(of_New_Clients__c), CALENDAR_MONTH(CloseDate)  FROM Opportunity  WHERE IsWon = true  AND CloseDate = THIS_YEAR  GROUP BY CALENDAR_MONTH(CloseDate)

This is the result:

This is the goal:
Frédéric Trébuchet
What if you change "SUM(of_New_Clients__c), CALENDAR_MONTH(CloseDate)" by "CALENDAR_MONTH(CloseDate), SUM(of_New_Clients__c)"? Not sure but month 25 looks strange!
Sorry, I don't know anything (yet) about reports.
May some help here http://www.salesforce.com/us/developer/docs/workbook_analytics/workbook_analytics.pdf.

Regards,
Fred

This was selected as the best answer
Frédéric Trébuchet
Hi,

Did you find the answer you want?
If so, can you mark this question as Solved and share with people.