Analytics SAQL Developer Guide

Overview
Quick Start
Examples
Analyze Your Data Over Time
Calculate How Long Activities Take
Display the Opportunities Closed This Month
Forecast Future Data Points with timeseries
Combine Data from Multiple Data Streams with cogroup
Replace Null Values with coalesce()
Dynamically Display Your Top Five Reps with Windowing
Append Datasets using union
Calculate the Slope of the Regression Line
Show the Top and Bottom Quartile
Calculate Grand Totals and Subtotals with the rollup Modifier and grouping() Function
SAQL Reference
Query Performance
PDF

Note: This release is in preview. Features described here don’t become generally available until the latest general availability date that Salesforce announces for this release. Before then, and where features are noted as beta, pilot, or developer preview, we can’t guarantee general availability within any particular time frame or at all. Make your purchase decisions only on the basis of generally available products and features.

Calculate How Long Activities Take

Use daysBetween() and date_diff() to calculate the difference between two dates or times.

Example: Display the Number of Days Since an Opportunity Opened

Suppose that you have an opportunity dataset with the account name and the epoch seconds fields:

Diagram showing the opportunity dataset.

You want to see how many days ago an opportunity was opened. Use daysBetween() and now(). Use toDate() to convert the order date epoch seconds to a date format that can be passed to daysBetween().

1q = load "OpsDates1";
2
3q = foreach q generate Account, daysBetween(toDate(OrderDate_sec_epoch), now()) as 'daysOpened';

The resulting data stream displays the number of days since the opportunity was opened.

Diagram showing the number of days each account has been opened for.

Example - How Many Weeks Did Each Opportunity Take to Close?

Use date_diff() with datepart = week to calculate how long, in weeks, it took to close each opportunity.

1q = load "DTC_Opportunity";
2q = foreach q generate date_diff("week", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch) ) as 'Weeks to Close';
3q = order q by 'Weeks to Close';