function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Willie MullenWillie 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ébuchetFré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
 

All Answers

Frédéric TrébuchetFré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
BalajiRanganathanBalajiRanganathan

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ébuchetFré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 MullenWillie 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ébuchetFré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 MullenWillie 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:
User-added image

This is the goal:
User-added image
Frédéric TrébuchetFré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ébuchetFré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.
If any you can also mark the answer which help you most as the best ;)

Regards,
Fred
Frédéric TrébuchetFrédéric Trébuchet
Thanks for points ;) but what helped you most, suggestion to change your query or link to workbook?

Regards,
Fred