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
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
|
[amount] [week to date] [monthly] |
For each month |
Amount of sales for each month, where:
And
Broken down
|
[amount] [week to date] [quarterly] |
For each quarter |
Amount of sales for each quarter, where:
And
Broken down
|
[amount] [week to date] [yearly] |
For each year |
Amount of sales for each year, where:
And
Broken down
|
[amount] [month to date] [monthly] |
For each month |
Amount of sales for each month, where:
Broken down
|
[amount] [month to date] [quarterly] |
For each quarter |
Amount of sales for each quarter, where:
And
Broken down
|
[amount] [month to date] [yearly] |
For each year |
Amount of sales for each year, where:
And
Broken down
|
[amount] [month to date] [quarterly] |
For each quarter |
Amount of sales for each quarter, where:
And
Broken down
|
[amount] [week to date] [weekly] [daily] |
For each week with breakdown by day |
Amount of sales for each week, where:
Broken down
|
[amount] [week to date] [monthly] [daily] |
For each month with breakdown by day |
Amount of sales for each month, where:
And
Broken down
|
[amount] [week to date] ‘january’ |
For each month filtered to January |
Aggregated amount of sales for each month of January, where:
And
|
[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:
And
|
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 |
