Compare table windowing and SAQL time series
The compare table's window functions and the SAQL they generate: row ranges, reset groups and ordering rules, rank versus dense rank, period-over-period grouping, and forecasting with fill and timeseries, including its licence requirement.
Given business requirements, use advanced functionality such as windowing and time series analysis within compare tables.
~7 min read · 8 practice questions ·
What the exam expects
- Pick the compare table function (Sliding Window, Percentage of Group, Rank Within Group, Period Over Period, Change from Previous, Running Total) from a business requirement, with the right reset group.
- Read and write SAQL windowing: row ranges,
partition by,order by, and the rules that make an expression invalid. - Predict ranking results with ties for
rank(),dense_rank(),cume_dist()androw_number(). - Forecast with
timeseries, preparing gaps withfill, handling a partial last period, and knowing what an org without the right licence gets back.
Key terms: compare table (a lens table whose columns hold formulas and window functions on measures); window function (an aggregate over a range of adjacent groups that doesn't change the row count); row range (the window, such as [-2 .. 0]); reset group (partition by columns that restart the calculation); timeseries (the SAQL statement that predicts future data points).
Key facts
- Detail
- Doesn't change the number of rows returned
[..] with sum, avg, min or max- Detail
order bynot allowed
- Detail
- Windowing not available
partition by all- Detail
- No reset boundaries
- Detail
- Group by year first; compound dates allowed
- Detail
- Can't be filtered
timeseries licence- Detail
- Growth or Plus for full results; otherwise 1 forecast period
timeseries position- Detail
- After a projection statement
ignoreLast default- Detail
false
predictionInterval- Detail
- 80 or 95
- Detail
- At least 24 data points
- Detail
- Last grouping sets the columns; cells show the first measure
fill gaps- Detail
- Adds missing periods with null values
Sources for this section (6)
- Windowingdeveloper.salesforce.com (opens in a new tab)
- Compare Table Functionshelp.salesforce.com (opens in a new tab)
- Custom Formulashelp.salesforce.com (opens in a new tab)
- timeseriesdeveloper.salesforce.com (opens in a new tab)
- Table Widgethelp.salesforce.com (opens in a new tab)
- filldeveloper.salesforce.com (opens in a new tab)
- Why doesn't adding a window column change a table's row count?
- When must a compare table formula use raw field names instead of A, B, C?
- What does
timeseriesreturn when it receives no data? - When does
fillignore itsstartDateandendDate?
The questions are free to read. Answers with their sources and self-grading are included with the pack.
Keep reading with the CRM Analytics and Einstein Discovery Consultant pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 26 lessons of the CRM Analytics and Einstein Discovery Consultant guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Practice exams purchase adds inline bank questions and topic practice (8 questions for this lesson)
- An official source cited for every claim
- An AI tutor that answers from the guide
- Choose guide-only, practice-only, or save $4 with both