The timeseries() function is one of the most powerful capabilities in CRM Analytics. It analyses your historical data, selects the forecasting model that gives the best statistical fit, detects seasonality and periodic cycles, and projects what your data will look like in the future — all without a data scientist in the room.
Out of the box, you can apply timeseries() to any existing dataset column at the Year and Month level. But there is a significant limitation that catches many practitioners by surprise.
timeseries() function in the Compare Table UI does not allow you to select a formula column as the input. If you create a calculated field — such as Quota Achievement Rate, Opportunity Win Rate, or any custom percentage — it will not appear in the column selection dropdown for timeseries forecasting. Only native dataset columns are available.The workaround is a clean two-part SAQL approach. You build the table using the UI first to capture the formula syntax, then switch to SAQL mode and make two targeted updates. Here is the complete process across six steps.
timeseries() function applied directly to an existing dataset column — showing the out-of-the-box behaviour before encountering the formula column limitation. Originally published at einsteinplusanalytics.com.Build your Compare Table using the visual query builder. Add your existing dataset columns and create the formula column you want to forecast. In the example below, the table includes:
— Monthly Units Sold (existing dataset column)
— Monthly Target (existing dataset column)
— Target Achievement Rate (formula column: Units Sold ÷ Target, formatted as a percentage)
At this stage, do not worry about timeseries. The goal of this step is simply to get the formula column created so that CRM Analytics generates the underlying SAQL syntax for it. You will need that syntax in Step 4.
foreach generate syntax automatically — you just copy it in Step 4.Since your formula column does not appear in the timeseries dropdown, select any existing dataset column from the list instead — for example, Monthly Units Sold. Apply the timeseries() function to it with your desired interval and forecast length.
This is a temporary placeholder. You are not interested in forecasting Units Sold — you are using it purely to generate the correct SAQL structure for the timeseries function, which you will then redirect to your formula column in Step 5.
With the table configured, switch the query from visual mode to SAQL mode. In the query editor toolbar, click the SAQL toggle button.
CRM Analytics will generate the full SAQL representation of everything you built in the UI — including the foreach generate statement for your formula column and the timeseries() call on your placeholder column. This is the SAQL you will edit in Steps 4 and 5.
In the generated SAQL, locate the foreach generate statement. This is where CRM Analytics computes row-level values. You will see your formula column expression somewhere in this block — something like:
// Inside the foreach generate block:
('Units_Sold' / 'Monthly_Target') * 100 as 'Achievement_Rate'
Copy this computed expression. Give it a clear, meaningful name — something you will recognise in Step 5. This named field is what you will pass into the timeseries() function in the next step.
timeseries() call in Step 5. SAQL field names are case-sensitive.foreach generate block, adding it as a named computed field, and verifying the naming for use in the timeseries call. Originally published at einsteinplusanalytics.com.Find the timeseries() function call in the SAQL. It currently references your placeholder column (Monthly Units Sold). Replace that column reference with the named formula field you computed in Step 4.
-- Before (placeholder column): timeseries('Units_Sold', ...) -- After (your formula field): timeseries('Achievement_Rate', ...)
Then clean up the SAQL — remove or comment out any unnecessary statements and extra fields that were added by the UI builder but are no longer needed. Keep only the columns you want to display in the final chart.
timeseries() call with the formula field name, removing redundant statements, and running the updated SAQL to confirm the forecast is generated correctly. Originally published at einsteinplusanalytics.com.Switch the widget type from Compare Table to a Timeline chart. Configure the chart properties:
— Set the time axis to your date dimension (Year-Month)
— Set the value axis to your Achievement Rate field
— Enable the Predictive line property to show the forecast segment as a distinct visual element
— Optionally add a reference line or confidence band
The chart now shows your historical Target Achievement Rate as a solid line, with the timeseries forecast extending forward as a predictive line — all driven by your custom formula column.
Two SAQL edits. Any formula. Full timeseries forecasting — no data science team required.