Category Archives: Uncategorized

Bucketing by Hour (Not Day)

Leave a reply

For reports that need to bucket by hours—not days, weeks, or months—create a formula like this.

  • It returns a date+time string in 'YY: MM/DD HHh format (like '22: 03/23 09h), which is the longest string I could fit in a graph label without Salesforce truncating it.
  • It uses the American schedule for Daylight Saving. I haven’t yet tweaked it for European Daylight Saving, which diverges from American DS for about six weeks each year
  • It’s formatted for the the United States’ Central time zone—if you’re in a different US timezone, tweak the 5/24 and 6/24 terms
IF(
    DATEVALUE(CreatedDate) >= DATE(YEAR(DATEVALUE(CreatedDate)), 3, 1) + 
    (14 - CASE(MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 3, 1) - 
    DATE(1900, 1, 7), 7), 0, 7, MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 3, 1) - DATE(1900, 1, 7), 7)))
    &&
    DATEVALUE(CreatedDate) < DATE(YEAR(DATEVALUE(CreatedDate)), 11, 1) + 
    (7 - CASE(MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 11, 1) - 
    DATE(1900, 1, 7), 7), 0, 7, MOD(DATE(YEAR(DATEVALUE(CreatedDate)), 11, 1) - DATE(1900, 1, 7), 7))),
    
    "'" & MID(TEXT(CreatedDate - 5/24), 3, 2) & ": " & 
        MID(TEXT(CreatedDate - 5/24), 6, 2) & "/" & MID(TEXT(CreatedDate - 5/24), 9, 2) & " " & 
            MID(TEXT(CreatedDate - 5/24), 12, 2),
    
    "'" & MID(TEXT(CreatedDate - 6/24), 3, 2) & ": " & 
        MID(TEXT(CreatedDate - 6/24), 6, 2) & "/" & MID(TEXT(CreatedDate - 6/24), 9, 2) & " " & 
            MID(TEXT(CreatedDate - 6/24), 12, 2)
) & "h"

The first clause in the huge IF statement determines whether we’re on Daylight Saving Time

The second clause returns the Daylight-Saving-formatted version of the date/time

The third clause returns the non-Daylight-Saving-formatted version of the date/time

Generate “Random” Numbers Declaratively in Salesforce

Leave a reply

Q: How can I use declarative configuration to generate random numbers in Salesforce?

A: The following field formula generates a number from 1 to 100 based on each record’s ID. It isn’t random, but with enough records created gradually over time I hope it’ll produce a reasonable-ish distribution between 1 and 100.

(
  (
    (
      (FIND(MID(Id, 14, 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz") - 1) * 62 +
      (FIND(MID(Id, 15, 1), "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz") - 1)
    ) * 99
  ) / 3843
) + 1

What’s going on here?

  • The two FIND() functions in the heart of the formula take the 14th and 15th characters of the Salesforce ID and convert them into a number between 0 and 3843 (which is the highest two-digit number in base62).
  • The three outer operations—the * 99, / 3843, and + 1—are what’s used to convert
    • from a number that ranges from 0 to 3843
    • into a number that ranges from 1 to 100.
  • The conversion logic is cribbed from a Stack Overflow post.

Testing it out, I just inserted 5000 Leads using Data Loader, which I thought would produce a uniform distribution of numbers (again, not random, but uniform).

As you can see, something (in how Salesforce processed the insert job, I guess) led to a doubly-high bulge between 62 and 95, and half the number of 1s and 100s:

Distribution of numbers between 1 and 100 driven by generated Salesforce IDs

My friend Chris Robertson applied it to a table with 3,906 records and got this:

. . . eh, still not great. Let me know what you get!

Better yet, let me know if you have any luck generating random numbers declaratively, say using other “random” number generators like CreatedDate and/or LastModifiedDate. I had no luck with them.