[SalesForce] Displaying month/year from createdDate in SOQL query

I am very new to Salesforce and struggling to get a query to return the data in the format I require – i.e pushing the results through Conga composer to feed a chart in a powerpoint template. The requirement is pretty simple, I want to count the number of leads created each month over a certain period of time and plot as Count vs month/year but I am struggling as I can only get the month as a number. The query I have at the minute is:

SELECT CALENDAR_MONTH(CreatedDate) month, 
COUNT(CreatedDate) leadcount 
FROM Lead 
WHERE CreatedDate>=LAST_YEAR 
AND CreatedDate<THIS_MONTH 
AND LeadSource not in ('MailChimp') 
GROUP BY CALENDAR_YEAR(CreatedDate), 
CALENDAR_MONTH(CreatedDate) 
ORDER BY 
CALENDAR_YEAR(CreatedDate), 
CALENDAR_MONTH(CreatedDate)

but this displays the data as:

month   leadcount
1       8
2       13
3       20
4       45

but I would like: 

month       leadcount
Jan 2016     8
Feb 2016     13
Mar 2016     20

etc

I tried decomposing the CreatedDate in 2 custom fields on the lead object Created_Month__c and Create_Year__c (both Formula(Text)) to hold just the month and year component respectively of the lead createdDate for each record but I cannot work out how ( or if I can ) merge these in the select and then group and order them in a similar manner to the original query.

Any suggestions would be welcome?

Best Answer

You can do 2 things:

1) Create new field and update it with workflow. Then you can use field in your query 

TEXT(YEAR(DATEVALUE(CreatedDate)))  & ' ' &
CASE(MONTH(DATEVALUE(CreatedDate)), 1, "January", 2, "February", 3, "March",  4, "April",  5, "May",  6, "June", 7, "July", 8, "August", 9, 
"September", 10, "October", 11, "November", 12, "December", "None")

2) Write query like this and merge month and year in conga 

SELECT CALENDAR_year(CreatedDate) Year, CALENDAR_MONTH(CreatedDate) month, 
COUNT(CreatedDate) leadcount 
FROM Lead 
WHERE CreatedDate>=LAST_YEAR 
AND CreatedDate<THIS_MONTH 
AND LeadSource not in ('MailChimp') 
GROUP BY CALENDAR_YEAR(CreatedDate), 
CALENDAR_MONTH(CreatedDate) 
ORDER BY 
CALENDAR_YEAR(CreatedDate), 
CALENDAR_MONTH(CreatedDate)

You can use if statement in conga to convert 1 to Jan and so on.

{IF “Expression1” Operator “Expression2” “TrueText” “FalseText” }
Related Topic