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()