It's common for businesses to want to take business days into account when showing data on a dashboard in CRM Analytics (formerly Tableau CRM) to compare those figures to the last year's same period. The problem is that CRM Analytics does not have a native set of functions to calculate business days; however, Sales Cloud does. We demo how to utilize Sales Cloud's business hours object and some custom Apex to generate business days to sync to Analytics for dashboard queries.
Setting Up Business Days Within Sales Cloud
Business Hours and Holidays
Before we dive into the Apex code, we have a few things to set up within Sales Cloud. To start, we need to set up our business hours and holidays. Business Hours is a standard object on the Salesforce platform that stores your company's business hours and holidays. Setting up your business hours allows for Salesforce to automatically determine what is a business day or not. Salesforce does not account for observable holidays, though, which means you will have to build out your own code in Apex to work around that.
After you have established your holidays and business hours within Sales Cloud, this information is ready to be used in your Apex code.
Business Day Object
To store your business day records, you will need to create a custom object. Ours is called "Business Day". Our object has one custom field, which holds the date. This object will be used to record each individual business day in a given year.
Using Apex Code to Determine Business Days
In order to create records in our Business Day object, we will need to leverage the isWithin standard Apex function on the Business Hours object. This function takes in the ID of your business hours and a Datetime variable as parameters and returns whether the given Datetime is within your business hours. In the example below, we use the ID of the default business hours and instantiate two Datetime variables, startDateTime and endDateTime.
Id businessHoursId = [SELECT Id FROM BusinessHours WHERE IsDefault = true LIMIT 1].Id;
if(startDate == null || endDate == null){
return;
}
delete [select id from business_day__c where date__c >= :startDate and date__c <= :endDate];
// Adding 12 hours to ensure the conversion of UTC to EST won't shift the date to the previous day
DateTime startDateTime = DateTime.newInstanceGMT( startDate.Year(), startDate.Month(), startDate.Day(), 12, 0, 0);
DateTime endDateTime = DateTime.newInstanceGMT( endDate.Year(), endDate.Month(), endDate.Day(), 12, 0, 0);
Using the Datetime variables from above, we iterate through the starting date up to the end date and use the isWithin function to determine if the day is a business day. If the current day is a business day, it then creates a new Business Day record and adds it to a list. After it has gotten to the end date, the list is then uploaded into your database, and ready to be used in Analytics Studio. We recommend having this logic for generating the records automated with a scheduled automation that runs on the first day of the year to eliminate the hassle of running this manually every year.
List<Business_Day__c> businessDays = new List<Business_Day__c>();
while(startDateTime <= endDateTime){
if(BusinessHours.isWithin(businessHoursId, startDateTime)){
Business_Day__c businessDay = new Business_Day__c();
businessDay.Date__c = startDateTime.Date();
businessDays.add(businessDay);
}
startDateTime = startDateTime.addDays(1);
}
insert businessDays;
Tableau CRM Analytics Studio
Once we have the list of Business Day records for this year, we can use them in Analytics Studio as a data set. In order to use our data set in Analytics Studio, we have to create a dataflow that inputs our Business Day records. This dataflow has only two steps. In the first step, we are pulling in the date field from our custom Business Day object.
The second step generates the data in Tableau CRM based on the parameters from the first step.
Once you create your data set, you are able to use it within SAQL queries and on your dashboards in Tableau CRM.Â
Conclusion
Creating a business days data set in Tableau CRM is a useful tool that can serve many purposes. You can create dashboards and limit SAQL queries to tailor your reports around your company's business days, making a unique experience specific to your company. If you need help customizing your Tableau CRM Analytics Studio and Sales Cloud experience, contact DB Services today!
Need help with your Salesforce digital transformation? Contact us to discuss Salesforce consulting, implementation, development, and support!
Download the Calculate Business Days in Tableau CRM Demo File
Please complete the form below to download your FREE Salesforce file.
