CRM Analytics: Dynamic table with Input Widget

Use case: give users the ability to show top N open opportunities based on Amount in a dashboard.

Step:

1. Add a table widget with the columns, set a query limit (example 10), and add filters as needed; for my use case, the filter isClosed = false and sorted by Amount.
2. Add an input widget and set the parameters in Input Values and Input Style.
3. Add a text widget to show the top X rows for the column.

Here is the result:

What needs to be configured?

• There is no need to change the input widget except the parameters mentioned above. 
• For the table widget to show top X records, we need to change the limit from 10 (example) with binding [ "{{cell(input_1.selection, 0, \"input\").asObject()}}" ]; input_1 is the Query ID of the input widget.
• For the text widget, click the "Add Query Data" button and select the Dynamic Data Source, Dynamic Text Field, and Interaction Type as below; you can manually add "Top " text after configuring the query data.

Reference:

• Create an Input Widget

CRM Analytics: Duration between Date or Date/Time Fields

In the SAQL samples blog, we briefly share samples of formulas for calculating duration between 2 date or date/time fields using daysBetween(start date, end date) and date_diff(datepart, start date, end date).

Let's see more details in this blog here and use the sample data below with the user timezone as GMT.

SAQL to calculate days between date/time with date field:

• daysBetween(toDate(DateTime1__c_sec_epoch), toDate(Date1__c_sec_epoch))
• date_diff("day", toDate(DateTime1__c_sec_epoch), toDate(Date1__c_sec_epoch))

Notes: 

• If date1 is after date2, the number of days returned is a negative number.

Analysis:

• date_diff() will simply compare the date value only, ignoring the time value. If you see rows #9, 10, and 11, the time value is removed, so the duration would be 2 days. The same behavior applies to all other rows.
• daysbetween() is a bit complicated; the formula will consider the time value in the calculation, but the result is truncated without decimal points; for example, row #11 -- start date = 2025-02-27T11:00:00.000Z, end date = 2025-03-01 is treated as 2025-03-01T00:00:00.000Z; exact time difference = 1 day 13 hours, the result is truncated as 1. take another example from row #2, where start date = 2025-03-02T23:45:00.000Z, exact time difference =  -1 day, -23 hours, -45 minutes, the result is truncated as -1.

Now, let us change the user timezone to Singapore, which is GMT+8.

The result in CRM Analytics is the same as before changing the user timezone. The Date/Time field will be converted into GMT timezone (for Custom Time Zone Support is not enabled).

If you need to calculate the duration in hours or minutes, you can only use date_diff(). Let's see some samples below. This is similar to the "day" datepart, which ignores the whole time; if we use "hour," it ignores minutes onwards.

Analysis: Rows #10 and 11 have the same result because the minute in DateTime1 is ignored; the same is true for rows #1 and 2, even though they are just 15 minutes different in the exact time difference, but they will be shown as 1 hour.

Check out this blog to calculate case aging excluding weekends.

Reference:

• date_diff()
• daysBetween()

CRM Analytics: Long Text Area field only show 255 chars

The Long Text Area field or Rich Text Area field allows users to enter up to 131,072 characters on separate lines, which by default is 32,768 when creating the field. However, the table widget in CRM Analytics only shows 255 characters. What is the cause?

By default, Precision is blank in the sync setup; you can navigate from Data Manager >>  Connections >> SFDC_LOCAL, click the object name, click the pencil icon next to the field name, enter the length of the characters as you need in the Precision box.

Salesforce: Case fields that cannot be removed from page layout

Certain Salesforce Case standard fields cannot be removed from the case page layout, but you can set them as read-only and tell your users to ignore those fields, it would be a good idea to move them to the bottom of the page. 

These fields are marked with a blue dot when you open the case page layout:

 

• Contact Name
• Status
• Priority
• Case Origin
• Subject
• Description
• Web Email

dsds

If you upgrade the form to a dynamic layout, the restriction that the fields must always be shown is no longer applied.

CRM Analytics: Calculate aging excluding weekends in Dataflow

We shared the Salesforce formula field to calculate aging without the weekend here. But can we replicate it in CRM Analytics? 

Here is the formula using dataflow in compute expression:

Full_Weeks (in Numeric)
floor(date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))/7) * 5

Remaining_Days (in Numeric)

date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch)) % 7

Start_Day (in Numeric)

day_in_week(toDate(CreatedDate_sec_epoch))

Weekend_Adjustment (in Numeric)

case when (Start_Day + Remaining_Days) > 7 then 2 

     when (Start_Day + Remaining_Days) == 7 then 1 else 0 end

Business_Days_Aging

Full_Weeks + Remaining_Days - Weekend_Adjustment

Notes:

• The floor(numeric) function rounds a numeric value down to the nearest integer. For example, floor(125.625) will return 125.
• For more information and samples on date_diff() function, check out this blog Duration between Date or Date/Time Fields. 
• The day_in_week(date) function returns the day of the week as numeric, where Sunday = 1, Monday = 2, etc.

Sample:

Convert SOQL result Date/Time field to Excel

Sample: SELECT Id, CreatedDate FROM Account

Result of CreatedDate: 2024-08-14T14:48:05.000+0000

To Convert into Excel as Date/Time format:

1. Formula =VALUE(SUBSTITUTE(LEFT(A2, 19), "T", " "))
2. Format Cells... (or Ctrl+1) for the above formula cell, select Custom, and Type: yyyy-mm-dd hh:mm:ss
3. Done