Learn About Common Formulas in Worksheets
Learning Objectives
After completing this unit, you’ll be able to:
- Describe the function of worksheets.
- Explain common date formulas in worksheets.
Before You Start
Before you start this module, make sure you complete this content. The work you do here builds on the concepts and work you do in that content.
Spiff Worksheets
In Salesforce Spiff, worksheets provide a way to create calculations that produce a single output, rather than a calculation that is applied to rows of filtered data such as a datasheet calculation.
Worksheets are more structured than spreadsheets, because fields have version history and calculations are contextual to the period, plan, and user selected in Spiff Designer. Worksheet calculations aren't associated with individual record details, or record details that are specific to a particular table or object. Think of worksheet calculations as global, general, or statement calculations.
Worksheets’ strength is their flexibility, as you can use them in data filters, in other worksheet fields, and even in datasheet fields tied to objects on non-related plans. Here are some examples.
- Store constant values, such as plan base commission rates.
- Maintain short lists of values to be used in other calculations or filters.
- Provide these snapshots for the selected context, similar to a miniature statement view.
- Rep’s name
- Is it the end of the quarter?
- Total renewals closed this month
- Rep’s quota amount
- Year-to-date (YTD) new business attainment
- Total count of meetings held by a sales development representative (SDR) this period
- Rep’s name
Commonly Used Calendar Date Formulas
When calculating for different times of the year, it’s helpful to create worksheet calculations that you can reference in data filters and throughout your calculations for various time periods. Here are some examples.
Asterisk (*) indicates calculations that depend on other calculations.
Calculation Name | Syntax |
|---|---|
Beginning of Year/Beginning of Q1 | beginning_of_year(statement_period.start_date) |
Beginning of Last Month | months_ago(statement_period.start_date,1) |
Beginning of Last Quarter | beginning_of_quarter(days_ago(beginning_of_quarter(statement_period.start_date),1)) |
Beginning of Last Statement Period* | beginning_of_month(EndOfLastStatementPeriod) |
Beginning of Last Year | beginning_of_year(days_ago(beginning_of_year(statement_period.start_date),1)) |
Beginning of Q1 Last Year* | months_Ago(BeginningOfQ1, 12) |
Beginning of Q2* | days_ago(EndOfQ1, -1) |
Beginning of Q2 Last Year* | months_ago(BeginningOfQ2, 12) |
Beginning of Q3* | days_ago(EndOfQ2, -1) |
Beginning of Q3 Last Year* | months_ago(BeginningOfQ3, 12) |
Beginning of Q4 * | days_ago(EndOfQ3, -1) |
Beginning of Q4 Last Year* | months_ago(BeginningOfQ4, 12) |
Beginning of Quarter | beginning_of_quarter(statement_period.start_date) |
Beginning of Two Periods Ago (if monthly periods) | months_ago(statement_period.start_date, 2) |
Beginning of Year | beginning_of_year(statement_period.start_date) |
End of Last Month | days_ago(statement_period.start_date, 1) |
End of Last Quarter | days_ago(beginning_of_quarter(statement_period.start_date),1) |
End of Last Statement Period | days_ago(statement_period.start_date, 1) |
End of Last Year | days_ago(beginning_of_year(statement_period.start_date),1) |
End of Period Two Periods Ago* | days_ago(BeginningOfLastMonth, 1) |
End of Q1 | end_of_quarter(beginning_of_year(statement_period.start_date)) |
End of Q1 Last Year* | months_ago(EndOfQ1, 12) |
End of Q2* | end_of_quarter(BeginningOfQ2) |
End of Q2 Last Year* | months_ago(EndOfQ2, 12) |
End of Q3* | end_of_quarter(BeginningOfQ3) |
End of Q3 Last Year* | months_ago(EndOfQ3, 12) |
End of Q4* | end_of_year(statement_period.start_date) |
End of Q4 Last Year* | months_ago(EndOfQ4, 12) |
End of Quarter | end_of_quarter(statement_period.start_date) |
End of Year | end_of_year(statement_period.start_date) |
Statement Period Month | month(statement_period.start_date) |
Calendar and Fiscal Year Dates for Half-Year Filters
If you’re using half-year data for quotas and filters, it's important to consider these three key dates.
- The beginning of the year
- The middle of the year
- The end of the year
Check out the calculations specific to these dates that you can use in a worksheet in Designer.
Asterisk (*) indicates calculations that depend on other calculations.
Calendar Year Calculations
Calculation Name | Syntax |
|---|---|
Beginning of Calendar Year (January 1) | beginning_of_year(statement_period.start_date) |
Mid Year (July 1)* | months_ago(BeginningOfCalendarYear, -6) |
Last Day of Calendar Year (December 31) | end_of_year(statement_period.end_date) |
Half Year Start* | if(statement_period.start_date < MidYear, BeginningOfCalendarYear, MidYear) |
Half Year End* | if(statement_period.start_date < MidYear, days_ago(MidYear, 1), LastDayOfCalendarYear) |
Data Filter Example* | =date_between?(CloseDate, HalfYearStart, HalfYearEnd |
Fiscal Year Calculations
Calculation Name | Syntax |
|---|---|
Beginning of Fiscal Year | beginning_of_fiscal_year(statement_period.start_date) |
Mid Fiscal Year* | months_ago(BeginningOfFiscalYear, -6) |
Last Day of Fiscal Year | end_of_fiscal_year(statement_period.start_date) |
Half Year Fiscal Start* | if(statement_period.start_date < MidFiscalYear, BeginningOfFiscalYear, MidFiscalYear) |
Half Year Fiscal End* | if(statement_period.start_date < MidFiscalYear, days_ago(MidFiscalYear, 1), LastDayOfFiscalYear) |
Data Filter Example* | =date_between?(CloseDate, HalfYearFiscalStart, HalfYearFiscalEnd) |
In this unit, you learned the key features of worksheets and commonly used date formulas and filters. Next, you build out a common calculation to show the YTD commissions paid on a rep’s statement.