Geopointe

Color Markers Using a Formula Field as Buckets

We love our Marker Color functionality, but sometimes you need to take it an extra step beyond what the UI allows. A great example of this is the ability to bucket date fields.

Let's use Last Activity Date as an example, to see on the map those accounts that have seen recent activity and those that have been neglected. If you colored the markers by Last Activity Date, you wouldn't have any options. It would just color by individual dates. That's not particularly useful.


Instead, you can use formula fields to group your dates into buckets. I'll give you two differing examples.

Note: As of the November 2017, Geopointe offers more flexibility and power when using dates and date ranges for color markers. Learn more about this feature. 

Actual Date Values
If you want to see which accounts have seen activity in a specific year or month, you can do that pretty easily. Here is a code example for the text formula field:
 
IF( LastActivityDate < DATE( 2013, 1, 1) , "2012 or Before", 
IF( LastActivityDate < DATE( 2014, 1, 1) && LastActivityDate >= DATE( 2013, 1, 1) , "2013", 
IF( LastActivityDate < DATE( 2015, 1, 1) && LastActivityDate >= DATE( 2014, 1, 1) , "2014", 
IF( LastActivityDate >= DATE( 2015, 1, 1) , "2015", 
IF( ISBLANK(LastActivityDate) , "No Activity", 
null)))))

The values returned by the formula field will be the values in the quotes. Go back to the Map, edit the data set, and color the markers by your new field. I chose to add color rules:

And here's how it looks! Apparently I've neglected Europe.

Smart Date Values
You can also code the formula field so it's figuring out the dates in a "smart" or progressive way. For example, I want to see who has seen activity in the last 30 days, 60 days, 90, days, etc., without having to hardcode a date.

Here is a sample formula field code for that:

IF(TODAY() - LastActivityDate = 0, "Today",
IF(TODAY() - LastActivityDate <= 30, "Last 30 Days",
IF(TODAY() - LastActivityDate <= 60, "Last 60 Days",
IF(TODAY() - LastActivityDate <= 90, "Last 90 Days",
IF(TODAY() - LastActivityDate <= 180, "Last 180 Days",
IF(TODAY() - LastActivityDate <= 360, "Last 360 Days",
IF(TODAY() - LastActivityDate > 360, "Over a year", "No Activity"
)))))))

This code is performing a calculation and counting the number of days between the last activity date and today. Again, I wanted to define my own color rules, so this is what I decided on:


And, if I look at my accounts in New York, I can see I'm doing better than Europe, but not much:


And there you have it! How to bucket date fields for Geopointe Markers.

How to Get Started

If you don't have a ton of experience with formula fields, Salesforce offers plenty of resources to get you started.

To create one, navigate to the object using the Object Manager in Salesforce Setup and click Fields and Relationships under the appropriate object. Create a new field, select the Formula Data Type, and choose text as the Formula Return Type.

If you need some assistance with Color Markers, check out our doc dedicated to them.