Blog

Software tips, techniques, and news.

Business Days in Salesforce CRM Analytics (Tableau CRM)

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.

youtube-preview

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.

salesforce business hours and holidays.

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.

salesforce business days object.

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.

salesforce sfdc digest business days.

The second step generates the data in Tableau CRM based on the parameters from the first step.

salesforce sfdc register business days.

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.

Salesforce Experience *
Terms of Use *
OPT-IN: I agree that I am downloading a completely free file with no strings attached. This file is unlocked, and I may use it for my business or organization as I see fit. Because I am downloading a free file, I agree that I should receive occasional marketing. I understand that I can OPT-OUT of these emails at anytime.
Sabra Rathbun headshot.
Sabra Rathbun

Sabra is an enthusiastic and dependable application developer who is motivated by helping clients and coworkers succeed. Her positive attitude, accommodating nature, and consistency make her a valuable team member and a reliable developer.