Displaying month/year from createdDate in SOQL query

Question

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?

Answer 1

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” }

Answer 2

The only method to accomplish this is by using AtulRajguru9's first suggestion. You'll need to update a text field on the Lead with a Workflow Rule to reflect the 3-char month. SOQL won't allow you to group by a formula field so you'll need to capture that value in a text field in order to GROUP BY.

CASE(MONTH(DATEVALUE(CreatedDate)), 1, "Jan", 2, "Feb", 3, "Mar",  4, "Apr",  5, "May",  6, "Jun", 7, "Jul", 8, "Aug", 9, 
"Sep", 10, "Oct", 11, "Nov", "Dec") & " " & TEXT(YEAR(DATEVALUE(CreatedDate)))

Within Conga, you could merge and chart the information in Excel and lean on Excel to transform the data point from YYYY-MM-DDThh:mm:ssZ to MMM but if you're using Powerpoint Charts in Conga, we don't have an ace up our sleeve because PowerPoint simply isn't designed to transform your data during the merge.