Period to date keywords

The Period-To-Date keywords are filters that apply a time range from the start of the current period and ends at present (now). Assume the following:

  • the fiscal year starts on January 1st;

  • the start of the week starts on Monday;

  • the current date is 24th September 2024.

  • the current date is excluded from the to date period.

Examples:

  • Week-to-Date returns data from 16th September 2024 to 23rd September 2024;

  • Month-to-Date returns data from 1st September 2024 to 23rd September 2024;

  • Quarter-to-Date returns data from 1st July 2024 to 23rd September 2024;

  • Year-to-Date returns data from 1st January 2024 to 23rd September 2024.

Date Bucket as a Grouping Columns

In ThoughtSpot when a Date or DateTime column is included in the search, you can select and change the represented column bucket granularity. This could be Daily, Weekly, Monthly, Quarterly, or Yearly. The combination of the Period-To-Date filter and the Date Bucket effects the resulting query plan and results returned.

Date Bucket Window is Smaller than Period-To-Date

The Period-To-Date applies the keyword filter to return the range of data. Month-To-Date will return data from 1st of the month to the present date. The Date Bucket Keyword will then break down the result by the date column.

Examples:

  • [sales] [month to date] [daily] will return a row of data for each date in the month. If the date were September 23rd, this would be 23 rows of data.

  • [sales] [month to date] [weekly] will return a row of data for each week in the month. If the date were September 23rd, this would result in 4 rows of data, where the final week would be a partial week.

Date Bucket Window is Equal-To or Larger than Period-To-Date

Where the Date Bucket is equal to or larger than the Period-to-Date range then the query is re-written as FOR EACH period.

Examples:

  • [sales] [month to date] [monthly] will return a row for each month for the period 1st Day of the Month to 23rd Day of the Month.

    • January: 1st to 23rd; February: 1st to 23rd; March: 1st to 23rd; April: 1st to 23rd;

  • [sales] [month to date] [quarterly] will return a row for each quarter for the period 1st Day of the Quarter to 23rd Day of the Quarter.

    • Q1: January: 1st to 23rd;

    • Q2: April: 1st to 23rd;

    • Q3: July: 1st to 23rd;

  • [sales] [month to date] [quarterly] will return a row for each quarter for the period 1st Day of the Quarter to 95th Day of the Quarter.

    • 2022: September: 1st to 23rd;

    • 2023: September: 1st to 23rd;

    • 2024: September: 1st to 23rd;

The reason including the FOR EACH keyword is that trend analysis provides a more interesting result than a single summary value. Without the FOR EACH query plan [sales] [month to date] [yearly] would only return a single month as the data point. With the FOR EACH query plan [sales] [month to date] [yearly] returns a data point for each year.

With FOR EACH PERIOD the current date is always included.

Date Bucket Combinations

The same pattern is applied when multiple date bucket keywords are included in the search. If the date buckets are smaller than the Period-to-Date range then the simple filter is applied. When at least one of the buckets is equal to or larger than the Period-to-Date then the for each query pattern is applied.

Examples:

  • [sales] [month to date] [daily][weekly] will return a weekly column, daily column and apply the filter for the 1st September to 23 September.

  • [sales] [month to date] [daily][monthly] will return a daily column, monthly column where data will be returned for each month from the 1st Day of the Month to 23rd Day of the Month.

Date Bucket as a Filter

In the majority of scenarios the date bucket keyword will act as a grouping column. However, it is possible for the date bucket to be applied as a filter. The rule that is applied is dependent upon the column of the filter. January is a monthly filter, therefore the monthly FOR EACH rule is applied.

Examples:

  • [sales] [month to date ‘january’] will return a single sales value for each January in the dataset for days 1st to 23rd.

Detailed Keyword

The detailed keyword is not considered a grouping date bucket. Therefore, the inclusion of this keyword does not modify the query behavior from the simple period filter to the for each query plan.

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.

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?
×