Period to date keywords

The following period to date keywords exist:

  • week to date

  • month to date

  • quarter to date

  • year to date

Behaviors

These filters have two behaviors that depend upon how they are used in conjunction with other date keywords. These secondary keywords are considered triggers, because they trigger a change in behavior of the to date keyword.

For each period

When a to date keyword is used in combination with a secondary date keyword at the same grain or higher, then the behavior is modified to a FOR EACH period.

Current period

If no secondary date keyword is included in the query or the secondary date keyword is at a lower grain, then a filter for the CURRENT PERIOD is applied.

Daily Weekly Monthly Quarterly Yearly

Week to date

Current period

FOR EACH PERIOD

Less than or equal to day number of week

FOR EACH PERIOD

FOR EACH PERIOD

Equal to week of month

AND

Less than or equal to day number of week

FOR EACH PERIOD

Equal to week of quarter

AND

Less than or equal to day number of week

FOR EACH PERIOD

Equal to week of year

AND

Less than or equal to day number of week

Month to date

Current period

Current period

FOR EACH PERIOD

Less than or equal to day number of month

FOR EACH PERIOD

Equal to month of quarter

AND

Less than or equal to day number of month

FOR EACH PERIOD

Equal to month of year

AND

Less than or equal to day number of month

Quarter to date

Current period

Current period

Current period

FOR EACH PERIOD

Less than or equal to day number of quarter

FOR EACH PERIOD

Equal to quarter of year

AND

Less than or equal to day number of quarter

Year to date

Current period

Current period

Current period

Current period

FOR EACH PERIOD

Less than or equal to day number of year

To date filtering rules

  • When acting as a filter for the CURRENT PERIOD, this behavior is similar to the behavior of the THIS PERIOD filter. However there is a notable difference.

    • THIS PERIOD is the complete period in question including any future days in the period. Whereas CURRENT PERIOD’s maximum boundary is less than TODAY as defined by the ThoughtSpot’s cluster timezone.

  • CURRENT Period

    • For ThoughtSpot Cloud connections today are excluded from the boundary (less than today’s date).

    • For ThoughtSpot’s Falcon database, today is included in the boundary (less than or equal to today’s date).

  • FOR EACH includes today in the boundary (less than or equal to today’s date).

Grouping and filtering

In the majority of scenarios the secondary date keyword will act as a grouping column. For example: In the following search, monthly is the secondary date keyword and is acting as a grouping column.

[sales] [month to date] [monthly]

A secondary date keyword could also be applied as a filter. The rule that is applied is dependent upon the column of the filter. Example: January is a monthly filter, therefore the monthly FOR EACH rule is applied.

[sales] [week to date] [‘january’] will return for all the months of January (every year of data):

  • the sum of sales as a single value

  • for today’s week of the month

  • up to and including today’s day of the week

[sales] [month to date] [‘january’] will return for all the months of January (every year of data):

  • the sum of sales as a single value

  • up to and including today’s day of the month

Not visualized columns

Chart settings include the ability to set tokens as not visualized. A not-visualized token does not change the behavior of the query. The to date logic is defined based upon the tokens that are defined in the base query.

Examples

Current period

Search Secondary keyword Example

[amount] [week to date]

n/a

Amount of sales for the current week (as defined by the calendar) with a transaction date of less than today.

[amount] [month to date]

n/a

Amount of sales for the current month (as defined by the calendar) with a transaction date of less than today.

[amount] [quarter to date]

n/a

Amount of sales for the current quarter (as defined by the calendar) with a transaction date of less than today.

[amount] [year to date]

n/a

Amount of sales for the current year (as defined by the calendar) with a transaction date of less than today.

[amount] [week to date] [daily]

Less than to date period

Amount of sales for the current week, broken down by Day with a transaction date of less than today.

[amount] [month to date] [daily|weekly]

Less than to date period

Amount of sales for the current month, broken down by Day|Week with a transaction date of less than today.

[amount] [quarter to date] [daily|weekly|monthly]

Less than to date period

Amount of sales for the current month, broken down by Day|Week|Month|Quarterly with a transaction date of less than today.

For each period

Search Secondary keyword Example

[amount] [week to date] [weekly]

For each week

Amount of sales for each week, where

  • the transaction date’s day of the week is less than or equal to today’s day of the week. Broken down

  • By week

[amount] [week to date] [monthly]

For each month

Amount of sales for each month, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

And

  • the transaction date’s week of the month is equal to today’s week of the month.

Broken down

  • By monthly

[amount] [week to date] [quarterly]

For each quarter

Amount of sales for each quarter, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

And

  • the transaction date’s week of the quarter is equal to today’s week of the quarter.

Broken down

  • By quarter

[amount] [week to date] [yearly]

For each year

Amount of sales for each year, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

And

  • the transaction date’s week of the year is equal to today’s week of the year.

Broken down

  • By year

[amount] [month to date] [monthly]

For each month

Amount of sales for each month, where:

  • the transaction date’s day of the month is less than or equal to today’s day of the month.

Broken down

  • By month

[amount] [month to date] [quarterly]

For each quarter

Amount of sales for each quarter, where:

  • the transaction date’s day of the month is less than or equal to today’s day of the month.

And

  • the transaction date’s month of the quarter is equal to today’s month of the quarter.

Broken down

  • By quarter

[amount] [month to date] [yearly]

For each year

Amount of sales for each year, where:

  • the transaction date’s day of the month is less than or equal to today’s day of the month.

And

  • the transaction date’s month of the year is equal to today’s month of the year.

Broken down

  • By year

[amount] [month to date] [quarterly]

For each quarter

Amount of sales for each quarter, where:

  • the transaction date’s day of the month is less than or equal to today’s day of the month.

And

  • the transaction date’s month of the quarter is equal to today’s month of the quarter.

Broken down

  • By quarter

[amount] [week to date] [weekly] [daily]

For each week with breakdown by day

Amount of sales for each week, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

Broken down

  • By week, then day

[amount] [week to date] [monthly] [daily]

For each month with breakdown by day

Amount of sales for each month, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

And

  • the transaction date’s week of the month is equal to today’s week of the month.

Broken down

  • By month and day

[amount] [week to date] ‘january’

For each month filtered to January

Aggregated amount of sales for each month of January, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

And

  • the transaction date’s week of the month is equal to today’s week of the month.

[amount] [week to date] ‘2021’ ‘2022’

For each year filtered to 2021 and 2022

Aggregated amount of sales for each year with years of 2021 and 2022, where:

  • the transaction date’s day of the week is less than or equal to today’s day of the week.

And

  • the transaction date’s week of the year is equal to today’s week of the year.

Using period to date keywords with a calendar

Period-to-date keywords work slightly differently depending on whether you are using the DEFAULT calendar or a custom calendar and whether you’re using ThoughtSpot’s Falcon in-memory database or an external database, like a cloud data warehouse.

When you use the DEFAULT calendar, data is available up to TODAY, whereas when you use a custom calendar it is not.

Default calendar Custom calendar

Falcon, date

Includes current date

Excludes current date

Falcon, datetime

Includes current date

Excludes current date

External database, date

Excludes current date

Excludes current date

External database, datetime

Includes current date

Excludes current date


Was this page helpful?