Use spreadsheet shortcuts for time-based analysis

Use Cube's time-based shortcuts in your Spreadsheet App to save time on planning and analysis by automatically calculating trailing and rolling date rollups. Time-based shortcuts are used before or after your time dimension members to fetch the rollups you need.

Available time-based shortcuts

Standard time-based shortcuts can be used to quickly fetch data within a standard time period, such as a quarter or year:

• QTD - Quarter-to-date
• TD or YTD - Year-to-date
• LTD - Lifetime-to-date
• ROY - Rest of year

Note: Quarter to Date (QTD) and Year to Date (YTD) can be used alone to fetch data in a standard calendar year and reflect your time dimension hierarchy when combined with a time dimension member (Mar-24 QTD).

Customizable time-based shortcuts can fetch data from the previous or next 1-99 months or periods. Replace the X in the shortcut below with the number of months or periods you want to fetch:

• TXM - Trailing custom number (X) of month(s)
• NXM - Next custom number (X) of month(s)
• TXP - Trailing custom number (X) of period(s)
• NXP - Next customer number (X) of period(s)

Use the shortcut most relevant to your time dimension schedule. If your team plans in monthly intervals, use the monthly shortcuts, and if your team plans weekly or in another alternative time interval, use period-based time shortcuts.

Time-based shortcuts will sum all months with data for a specified scenario. Ending balance accounts will always use the latest period's balance. Beginning balance accounts will use the earliest period's balance.

For example, if there are actuals for January and February 2024, fetching Mar-24 T3M (trailing three months) would only include January & February. For ending balance accounts, fetching Mar-24 T3M would return the most recent balance from February, while the beginning balance accounts would return the earliest balance from January.

Use time-based shortcuts

Usage requirements

For optimal flexibility, some time-based shortcuts can be used alone or with parent dimension members. Use these guidelines to find the time-based shortcut that best suits your planning needs.

Note: Using YTD or QTD with a time dimension (Mar-24 YTD) will fetch within your fiscal year based on your Cube time dimensions (for example, if you have a nonstandard calendar, fetching Mar-24 YTD will return data from the beginning of your fiscal year through March).

Using YTD or QTD without a time dimension will fetch within a standard calendar year (will always be January through December).

Syntax

Add a time-based shortcut before or after your time dimension member in your spreadsheet headers, then fetch to your range. Cube will sum the values for the designated scenario, inclusive of the date used.

Valid separators between the date and time-based shortcut include:

Examples

Pulling trailing and rolling actuals and budget data is useful for variance analysis and understanding exactly where our finances stand at this point in the year without writing spreadsheet formulas.

In the example spreadsheet below, there are three time-based shortcuts used to fetch data from my Actuals and Budget scenarios in Cube:

• Sept-23 QTD will fetch a rollup of my Actuals data for the quarter, including September (a consolidation of July, August, and September).
• Sept-23 T12M will fetch a rollup of my Actuals data for the past 12 months, including September 2023.
• Oct-23 ROY will fetch my Budget data for the rest of the year - October, November, and December.

Time-based totals are calculated for scenarios and data for the referenced time periods. If calculations aren’t tying out, verify that this scenario is up to date by drilling into the total, fetching the associated time periods without the shortcut, or following the steps in this article.