It is sometimes desirable to display month names instead of month numbers in your charts. Month numbers can be especially confusing when you are using fiscal years. For example "2024-01"... Is it January? Or is it July, when the company= fiscal year starts? As obvious as it sounds, it is not easy to achieve this in Salesforce CRM Analytics. There are two ways to achieve this: using a recipe or employing SAQL (Salesforce Analytics Query Language).

Two ways to skin a cat

To showcase both techniques we will visualize Opportunity expected amounts grouped by year and month.

In the first method we will use a recipe to create an extra field with a month name.

In the second method we will include case method into the query itself.

Recipe

1. We require data from Opportunity object, specifically Close Date and Expected Amount fields. Create a Connection and synchronize the data.
2. Create a new Recipe with Opportunity connected dataset as an input.
3. Add a transformation node with the following custom formula built upon the field Close Date and save the outcome in a new field called CloseDate_MonthName:
   

   CASE
       when MONTH("CloseDate")=1 then 'Jan'
       when MONTH("CloseDate")=2 then  'Feb'
       when MONTH("CloseDate")=3 then  'Mar'
       when MONTH("CloseDate")=4 then  'Apr'
       when MONTH("CloseDate")=5 then  'May'
       when MONTH("CloseDate")=6 then  'Jun'
       when MONTH("CloseDate")=7 then  'Jul'
       when MONTH("CloseDate")=8 then  'Aug'
       when MONTH("CloseDate")=9 then  'Sep'
       when MONTH("CloseDate")=10 then  'Oct'
       when MONTH("CloseDate")=11 then  'Nov'
       when MONTH("CloseDate")=12 then  'Dec'
       else ''
       end


4. Save the output in the new dataset.
5. On the dashboard, create a new column chart based on the recipe from the previous steps. As Bars use Close Date (Year), Close Date (Month) and the new CloseDate_MonthName field. Use sum of Expected Amounts for Bar Length. Because the field Close Date (Month) is there just for sorting, hide it.

SAQL

1. For start create another column chart from the same dataset. This time use Close Date (Year) and Close Date (Month) for the Bars.

2. Next, open the query in query mode and use case method to transform the month numbers intto month names.

   q = load "AS_Opportunities";
   q = group q by ('CloseDate_Year', 'CloseDate_Month');
   q = foreach q generate q.'CloseDate_Year' as 'CloseDate_Year', q.'CloseDate_Month' as 'CloseDate_Month', (case when q.'CloseDate_Month' in ["01","1"] then "Jan" when q.'CloseDate_Month'  in ["02","2"] then "Feb" when q.'CloseDate_Month' in ["03","3"] then "Mar" when q.'CloseDate_Month' in ["04","4"] then "Apr" when q.'CloseDate_Month' in ["05","5"] then "May" when q.'CloseDate_Month' in ["06","6"] then "June" when q.'CloseDate_Month' in ["07","7"] then "Jul" when q.'CloseDate_Month' in ["08","8"] then "Aug" when q.'CloseDate_Month' in ["09","9"] then "Sep" when q.'CloseDate_Month' == "10" then "Oct" when q.'CloseDate_Month' == "11" then "Nov" when q.'CloseDate_Month' == "12" then "Dec" else "N/A" end) as 'Close Date Month Name', sum(q.'ExpectedRevenue') as 'B';
   q = order q by ('CloseDate_Year' asc, 'CloseDate_Month' asc);
   q = limit q 2000;


3. Hide the original column CloseDate_Month.

Which one is better?

If you did everything right, both options will provide exactly the same results. But which way is better?

From a performance point of view, the query without additional processing is going to be faster. When it comes to effort, I don’t believe that you would want to adjust SAQL every time you need to use a month name on your chart.

This leaves the SAQL approach for situations when the usage of month names is a one-time thing, you do not have access to the Data Manager, or adjusting the dataset is, for whatever reason, out of the question.

Looking for an experienced Salesforce Architect?

• Are you considering Salesforce for your company but unsure of where to begin?
• Planning a Salesforce implementation and in need of seasoned expertise?
• Already using Salesforce but not fully satisfied with the outcome?
• Facing roadblocks in your Salesforce implementation and require assistance to progress?

Feel free to review my profile and reach out for tailored support to meet your needs!