English (US) keyword reference

Use keywords to help define a search.

Note that you can only use one set of keywords at a time. If you set your locale to English, you can only use English keywords. You can’t also use keywords in any other language.

General

When using the top or bottom keywords without specifying a number (n), the number defaults to 10.
top n

Generates the top n items from a sorted result. If your search has more than 1 measure, ThoughtSpot generates the top n items from the first measure in the search.

Examples
top 10 sales rep revenue

top sales rep by count sales for average revenue > 10000

sales rep average revenue for each region top
top n measure1 by attribute|measure2

Calculates top n items, then sorts the top items by another measure or attribute.

Contrast with top n (swaps the order of operations).

Example
top 10 sales rep revenue by profit margin
top n …​ ranked by

Ranks the top n items by a specified measure. The ranked by keyword only works in conjunction with the top keyword. If your search has more than 1 measure, ThoughtSpot generates the top n items from the first measure in the search.

Examples
top 10 sales rep revenue sales ranked by sales

top customer revenue generated # of ads clicked ranked by # of ads clicked
bottom n

Generates the bottom n items from a sorted result.

Examples
bottom 25 customer by revenue for each sales rep

bottom revenue average

bottom revenue by state

customer by revenue for each sales rep bottom
sort by

Sorts the result set by an attribute or measure.

Examples
revenue by state sort by average revenue descending

revenue by customer sort by region sort by state
by <measure>

Treats the measure as an attribute and groups the result set by it.

Examples
cost by revenue

product by quantity purchased

Date

The last keyword returns the last n full periods and does not include the current period. For example, last 6 hours returns the last 6 full hours and does not include the current hour. To include the current period, add this <period>. For example, last 6 hours this hour.
after
Example
order date after 10/31/2014
before
Example
order date before 03/01/2014
between ... and ...
Example
order date between 01/30/2012 and 01/30/2014
daily
Example
shipments by region daily
daily year-over-year
Example
growth of revenue by order date daily year-over-year
day
Example
count monday restaurant
day of week
Example
revenue by day of week last 6 months
day of week
Example
count shipments Monday
growth of …​ by ...
Example
growth of sales by order date
growth of …​ by …​ daily
Example
growth of sales by order date daily
growth of …​ by …​ monthly
Example
growth of sales by date shipped monthly sales > 24000
growth of …​ by …​ quarterly
Example
growth of sales by date shipped quarterly
growth of …​ by …​ weekly
Example
growth of sales by receipt date weekly for pro-ski2000
growth of …​ by …​ yearly
Example
growth of sales by date closed yearly
hourly
Example
visitors by page name hourly
last day by
Example
customers last day by referrer
last month
Example
customers last month by store
last month by
Example
customers last month by day
last n days
Example
visitors last 7 days
last n months
Example
visitors last 10 months by day
last n quarters
Example
visitors last 2 quarters by month by campaign
last n weeks
Example
visitors last 10 weeks by day
last n years
Example
visitors last 5 years by revenue for sum revenue > 5000
last quarter
Example
customers last quarter sale > 300
last week
Example
customers last week by store
last year
Example
top 10 customers last year by sale by store for region west
month
Example
revenue by month last year
month
Example
commission January
month to date
Example
sales by product month to date sales > 2400
month year
Example
commission by sales rep February 2014
monthly
Example
commission > 10000 monthly
monthly year-over-year
Example
growth of revenue
  by receipt date
  monthly year-over-year
n days ago
Example
sales 2 days ago
last n days for each month
Example
sales last 2 days for each month
last n days for each quarter
Example
revenue last 15 days for each quarter
last n days for each week
Example
total sold last 2 days for each week
last n days for each year
Example
revenue last 300 days for each year
last n hours for each day
Example
sales last 2 hours for each day
n months
Example
visitors last 6 months for homepage visits > 30 by month
n months ago
Example
sales 2 months ago by region
last n months for each quarter
Example
cost last 2 months for each quarter
last n months for each year
Example
last 8 months for each year
n quarters ago
Example
sales 4 quarters ago by product name contains deluxe
last n quarters for each year
Example
last 2 quarters for each year
n weeks ago
Example
sales 4 weeks ago by store
last n weeks for each month
Example
sales last 3 weeks for each month
last n weeks for each quarter
Example
last 2 weeks for each quarter
last n weeks for each year
Example
last 3 weeks for each year
n years
Example
opportunities next 5 years by revenue
n years ago
Example
sales 5 years ago by store for region west
next day
Example
shipments next day by order
next month
Example
appointments next month by day
next n days
Example
shipments next 7 days
next n months
Example
openings next 6 months location
next n quarters
Example
opportunities next 2 quarters by campaign
next n weeks
Example
shipments next 10 weeks by day
next n years
Example
projected deals next 5 years
next quarter
Example
opportunities next quarter amount > 30000
next week
Example
shipments next week by store
next year
Example
opportunities next year by sales rep
quarter to date
Example
sales by product quarter to date
  for top 10 products by sales
quarterly
Example
sales quarterly for each product
quarterly year-over-year
Example
growth of revenue by date shipped
quarterly year-over-year
this day
Example
shipments this day by order
this month
Example
appointments this month by day
this quarter
Example
opportunities this quarter amount > 30000
this week
Example
shipments this week by store
this year
Example
opportunities this year by sales rep
today
Example
sales today by store
week
Example
revenue by week last quarter
week to date
Example
sales by order date week to date for pro-ski200
weekly
Example
revenue weekly
weekly year-over-year
Example
growth of revenue by date shipped
  weekly year-over-year
year
Example
revenue by product 2014 product name contains snowboard
year to date
Example
sales by product year to date
yearly
Example
shipments by product yearly
yesterday
Example
sales yesterday for pro-ski200 by store

Time

The last keyword returns the last n full periods and does not include the current period. For example, last 6 hours returns the last 6 full hours and does not include the current hour. To include the current period, add this <period>. For example, last 6 hours this hour.
detailed
Example
ship time detailed
last hour
Example
count unique visits last hour
last minute
Example
count homepage views last minute
n hours
Example
count visitors [ last | next ] 12 hours
n hours ago
Example
sum inventory by product by store 2 hours ago
n minutes
Example
count visitors [ last | next ] 30 minutes
n minutes ago
Example
sum inventory by product 10 minutes ago
next hour
Example
count projected visitors next hour
next minute
Example
count projected visitors next minute
this hour
Example
count visitors this hour
this minute
Example
count visitors this minute

Text

begins with Update
Examples
product name begins with 'pro'
  // returns all products that start with 'pro'

product name begins with 'pro'
  product name begins with 'sport'
  // returns all products that start with 'pro'
  // OR  'sport'

product name begins with 'pro'
  product color begins with 'bl'
  // returns all products that start with 'pro'
  // AND have color that starts with 'bl' (blue or black)
contains
Example
product name contains "alpine" description
  contains "snow shoe"
ends with
Example
product name ends with 'deluxe'
similar to
Example
course name similar to 'hand'
not begins with
Example
product name not begins with "tom's"
not contains
Example
product color not contains 'tan'
  product color not contains 'red'
not ends with
Example
product name not ends with "trial"
not similar to
Example
course name not similar to 'hand'

Starting with release 6.3,

  • When you use more than one begins with or ends with keyword in a search or formula on the same column, ThoughtSpot returns your results using an OR condition, instead of an AND condition.

  • When you use a combination of begins with and ends with, ThoughtSpot still returns your results using the AND condition.

  • When using multiple begins with on different columns, ThoughtSpot returns results using the AND condition.

For example, if you search for state name begins with "V" state name begins with "C", your results are Virginia, Vermont, California, and Connecticut. If you search for state name begins with V state name ends with T, your only result is Vermont.

Number

average
Example
average revenue by store
count
Example
count visitors by site
max
Example
max sales by visitor by site
min
Example
min revenue by store by campaign for cost > 5000
standard deviation
Example
standard deviation revenue by product by month for date after
10/31/2010
sum
Example
sum revenue
unique count
Example
unique count visitor by product page last week
variance
Example
variance sale amount by visitor by product for last year

Comparative

all
Example
revenue asia vs all
The all keyword can only be used as part of a versus phrase.
between... and...
Example
revenue between 0 and 1000
= (equal)
Example
unique count visitor by store purchased products = 3 for last 5
days
everything
Example
revenue asia vs everything
The everything keyword can only be used as part of a versus phrase.
> (greater than)
Example
sum sale amount by visitor by product for last year sale amount > 2000
>= (greater than or equal)
Example
count calls by employee lastname >= m
< (less than)
Example
unique count visitor by product by store for sale amount < 20
<= (less than or equal)
Example
count shipments by city latitude <= 0
!= (not equal)
Example
sum sale amount region != canada date != last 5 days
vs, versus
Example
revenue east vs west
ThoughtSpot doesn’t support the use of the vs keyword within an in or not in subquery.

You can use special constants for null and empty values with the = and != keywords: {null} and {empty}. For example: customer name = {empty} or department != {null}. NULL values are missing or invalid (for example, dividing by 0) values. ThoughtSpot also shows NULL if you have n/a in your data. Empty values are empty strings of text or strings containing only whitespace (spaces, tabs).

Location

farther than n miles|km|meters from …​
Example
average hours worked branch farther than 80 km from scarborough
near
Example
revenue store name county near san francisco
near …​ within n miles|km|meters
Example
revenue store name county near alameda within 50 miles

Period

day
Example
orders by day
day of month
Example
sales day of month by month
day of quarter
Example
deals day of quarter by year
day of week
Example
customers by week day of week
day of year
Example
ad clicks day of year by year
hour
Example
visitors by hour weekly
month of quarter
Example
purchases month of quarter by year
quarter
Example
purchases by quarter
quarter of year
Example
opportunities quarter of year last 4 years yearly
week of month
Example
product shipments week of month yearly
ThoughtSpot uses ISO week format for the week of [month | quarter | year] keywords, which means that the last few days of a quarter may sometimes show as the first few days of the next quarter, based on the ISO week date system.
week of quarter
Example
revenue week of quarter
ThoughtSpot uses ISO week format for the week of [month | quarter | year] keywords, which means that the last few days of a quarter may sometimes show as the first few days of the next quarter, based on the ISO week date system.
week of year
Example
new products week of year last 3 years yearly
ThoughtSpot uses ISO week format for the week of [month | quarter | year] keywords, which means that the last few days of a quarter may sometimes show as the first few days of the next quarter, based on the ISO week date system.

In / Not in

Searches with the in keyword do not include {null} values. To include these {null} values, create a formula for the relevant attribute in your search, to convert {null} values to 'unknown,' or some similar word.
in

Query in query search (intersection of two sets). Must match last attribute before keyword with the first attribute inside subsearch. The first attribute in the sub-string must match the attribute prior to the in keyword.

Syntax
attribute in (attribute subsearch)
Examples
store name in (top 10 store name by sales footwear)

product name 2014 product name in (product name 2013) sales

revenue by store name in (top 10 store name by sales footwear)
not in

Relative complement of two sets. Must match last attribute before keyword with first attribute inside subsearch. Doesn’t support use of the vs keyword.

Syntax
attribute not in (attribute subsearch)
Example
product name 2014 product name not in (product name 2013) sales

Find sales for all products ordered in 2014 that were not ordered in 2013

Starting with release 6.2, searches with the in keyword do not include {null} values. To include these {null} values, create a formula for the relevant attribute in your search, to convert {null} values to 'unknown,' or some similar word.

Was this page helpful?
×