Chapter 11 Keywords

Keyword refers to entering key words or phrases in the search box of the search analysis module, and DataFocus Cloud will automatically prompt to make the search more intelligent.

11.1 Date Keywords

  • Directly type in the year, e.g.: 2022

Table 11-1 Date Keywords

Keyword Description Case
Monday/Mon/Mon.
Tuesday/Tue/Tue.
Wednesday/Wed/Wed.
Thursday/Thu/Thu.
Friday/Fri/Fri.
Saturday/Sat/Sat.
Sunday/Sun/Sun.		 Mainly to view the data of a specific day of the week. e.g.: age creation_date Monday
weekend Mainly to view the data of weekends. e.g.: age creation_date weekend
January/Jan/Jan.
February/Feb/Feb.
March/Mar/Mar.
April/Apr/Apr.
May/May.
June/Jun/Jun.
July/Jul/Jul.
August/Aug/Aug.
September/Sep/Sep.
October/Oct/Oct.
November/Nov/Nov.
December/Dec/Dec.		 Mainly to view the data of a specific month of the year. e.g.: age creation_date January
yyyy Mainly to view data in a certain year. e.g.: 2021 Sales
before Mainly to view data before a certain time.
Time format: “yyyy”, “yyyy/mm”, “yyyy/mm/dd”, “yyyy/mm/dd hh:mm:ss”		 e.g.: creation_date before “2021/09/09” Sales
after Mainly to view data after a certain date.
Time format: “yyyy”, “yyyy/mm”, “yyyy/mm/dd”, “yyyy/mm/dd hh:mm:ss”		 e.g.: creation_date after “2021/09” Sales
between and Mainly to view the data between two dates.
Time format: “yyyy”, “yyyy/mm”, “yyyy/mm/dd”, “yyyy/mm/dd hh:mm:ss”		 e.g.: creation_date between “2020/09/09 12:30:00” and “2021/09/09 12:30:00”
last day Mainly to view the data of yesterday. Similar to “yesterday”. e.g.: age creation_date last day
last
Monday/Mon/Mon.
Tuesday/Tue/Tue.
Wednesday/Wed/Wed.
Thursday/Thu/Thu.
Friday/Fri/Fri.
Saturday/Sat/Sat.
Sunday/Sun/Sun.		 Mainly to view the data of a specific day of the last week. e.g.: age creation_date last Monday
last weekend Mainly to view the data of last weekend. e.g.: age creation_date last weekend
last week Mainly to view the data of last week. e.g.: age creation_date last week
last month Mainly to view the data of last month. e.g.: age creation_date last month
last quarter Mainly to view the data of last quarter. e.g.: age creation_date last quarter
last year Mainly to view the data of last year. e.g.: age creation_date last year
last xx days Mainly to view the data of the past xx days. e.g.: age creation_date last 4 days
last xx weeks Mainly to view the data of the past xx weeks. e.g.: age creation_date last 4 weeks
last xx months Mainly to view the data of the past xx months. e.g.: age creation_date last 4 months
last xx quarters Mainly to view the data of the past xx quarters. e.g.: age creation_date last 4 quarters
last xx years Mainly to view the data of the past xx years. e.g.: age creation_date last 4 years
yesterday Mainly to view the data of yesterday. e.g.: age creation_date yesterday
tomorrow Mainly to view the data of tomorrow. e.g.: age creation_date tomorrow
today Mainly to view the data of today. e.g.: age creation_date today
week to date Mainly used to view the data from current week to the present. e.g.: age creation_date week to date
month to date Mainly used to view the data from current month to the present. e.g.: age creation_date month to date
quarter to date Mainly used to view the data from current quarter to the present. e.g.: age creation_date quarter to date
year to date Mainly used to view the data from current year to the present. e.g.: age creation_date year to date
next day Mainly to view the data of tomorrow. Similar to “tomorrow”. e.g.: age creation_date next day
next
Monday/Mon/Mon.
Tuesday/Tue/Tue.
Wednesday/Wed/Wed.
Thursday/Thu/Thu.
Friday/Fri/Fri.
Saturday/Sat/Sat.
Sunday/Sun/Sun.		 Mainly to view the data of a specific day of the next week. e.g.: age creation_date next Monday
next weekend Mainly to view the data of next weekend. e.g.: age creation_date next weekend
next week Mainly to view the data of next week. e.g.: age creation_date next week
next month Mainly to view the data of next month. e.g.: age creation_date next month
next quarter Mainly to view the data of next quarter. e.g.: age creation_date next quarter
next year Mainly to view the data of next year. e.g.: age creation_date next year
next xx days Mainly to view the data of the next xx days. e.g.: age creation_date next 4 days
next xx weeks Mainly to view the data of the next xx weeks. e.g.: age creation_date next 4 weeks
next xx months Mainly to view the data of the next xx months. e.g.: age creation_date next 4 months
next xx quarters Mainly to view the data of the next xx quarters. e.g.: age creation_date next 4 quarters
next xx years Mainly to view the data of the next xx years. e.g.: age creation_date next 4 years
by day Mainly to group the query result according to 31 days (add up data according to 31 days of 1 month). e.g.: creation_date by day
by day of week Mainly to group the query result according to weekly date (add up data according to 7 days of 1 week). e.g.: creation_date by day of week
by week Mainly to group the query result according to 53 weeks (add up all the data according to 53 weeks of 1 year). e.g.: age creation_date by week
by month Mainly to group the query result according to 12 months (add up all the data according to 12 months of 1 year). e.g.: age creation_date by month
by quarter Mainly to group the query result according to 4 quarters (add up all the data according to 4 quarters of 1 year). e.g.: age creation_date by quarter
by year Mainly to group the query result according to years. e.g.: age creation_date by year
daily Mainly to group the query result according to day. e.g.: age creation_date daily
weekly Mainly to group the query result according to week. e.g.: age creation_date weekly
monthly Mainly to group the query result according to month (add up data from the beginning to the end of each month). e.g.: age creation_date monthly
quarterly Mainly to group the query result according to quarter (add up data from the beginning to the end of each quarter). e.g.: Age creation_date quarterly
yearly Mainly to group the query result according to year (add up data from the beginning to the end of each year). e.g.: age creation_date yearly
first xx days for each week/month/ quarter/year Mainly to group the data by time, and to view the data of the first xx days of each group. e.g.: first 2 days for each week
first xx weeks for each month/ quarter/year Mainly to group the data by time, and to view the data of the first xx weeks of each group. e.g.: first 2 weeks for each month
first xx months for each quarter/year Mainly to group the data by time, and to view the data of the first xx months of each group. e.g.: first 2 months for each month
first xx quarters for each year Mainly to group the data by time, and to view the data of the first xx quarters of each group. e.g.: first 2 quarters for each year
last xx days for each week/month/ quarter/year Mainly to group the data by time, and to view the data of the last xx days of each group. e.g.: last 2 days for each week
last xx weeks for each month/ quarter/year Mainly to group the data by time, and to view the data of the last xx weeks of each group. e.g.: last 2 weeks for each month
last xx months for each quarter/year Mainly to group the data by time, and to view the data of the last xx months of each group. e.g.: last 2 months for each year
Last xx quarters for each year Mainly to group the data by time, and to view the data of the last xx quarters of each group. e.g.: last 2 quarters for each year
xx days ago Mainly to query all data before xx days e.g.: 2 days ago
xx weeks ago Mainly to query all data before xx weeks e.g.: 2 weeks ago
xx months ago Mainly to query all data before xx months e.g.: 2 months ago
xx quarters ago Mainly to query all data before xx quarters e.g.: 2 quarters ago
xx years ago Mainly to query all data before xx years e.g.: 2 years ago

11.2 Time Keywords

Table 11-2 Time Keywords

Keyword Description Case
last minute Mainly to view the data of the last 1 minute. e.g.: age creation_date last minute
last hour Mainly to view the data of the last 1 hour. e.g.: age creation_date last hour
last xx minutes Mainly to view the data of the last xx minutes. e.g.: age creation_date last 5 minutes
last xx hours Mainly to view the data of the last xx hours. e.g.: age creation_date last 5 hours
hourly Mainly to view the data grouped by hour. e.g.: age creation_date hourly
next minute Mainly to view the data of the next 1 minute. e.g.: age creation_date next minute
next hour Mainly to view the data of the next 1 hour. e.g.: age creation_date next hour
next xx minutes Mainly to view the data of the next xx minutes. e.g.: age creation_date next 5 minutes
next xx hours Mainly to view the data of the next xx hours. e.g.: age creation_date next 5 hours
first xx hours for each day Mainly to view the data within the first xx hours of each day. e.g.: age creation_date first 5 hours for each day
last xx hours for each day Mainly to view the data within the last xx hours of each day. e.g.: age creation_date last 5 hours for each day
xx minutes ago Mainly to view the data xx minutes ago. e.g.: age creation_date 5 minutes ago
xx hours ago Mainly to view the data xx hours ago. e.g.: age creation_date 5 hours ago

11.3 String Keywords

  • When entering a string keyword in the search box, use quotation marks. Example: user_name begins with “Tom”

  • Conditional juxtaposition can be achieved by using commas between multiple values. Example: user_name begins with “Tom”, “Mary”

Table 11-3 String Keywords

Keyword Description Case
begins with Mainly to view the data who starts with this string. e.g.: user_name begins with ‘Tom’
contains Mainly to view the data who contains this string. e.g.: user_name contains ‘Tom’
ends with Mainly to view the data who ends with this string. e.g.: user_name ends with ‘Tom’
not begins with Mainly to view the data who does not start with this string. e.g.: user_name not begins with ‘Tom’
not contains Mainly to view the data who does not contain this string. e.g.: user_name not contains ‘Tom’
not ends with Mainly to view the data who does not end with this string. e.g.: user_name not end with ‘Tom’

11.4 Filter Keywords

  • When entering a string keyword in the search box, use quotation marks. Example: user_name begins with “Tom”

Table 11-4 Filter Keywords

Keyword Description Case
is null, is not null Filter for values in Attribute column
Note: Date columns can’t use “date is null” directly, but can use “isnull(date)”		 e.g.: name is null
>, <, >=, <= Filter for values in Measure column e.g.: age > 20
=, != Filter for values in Attribute or Measure column
Note: use quotation marks for values in the connected columns, and commas can be used between the values in multiple columns to achieve conditional juxtaposition.		 e.g.: sex = “male”, Product_Name = “corn”, “pea”

11.5 Sort Keywords

  • Applies to Attribute columns and Measure columns.

Table 11-5 Sort Keywords

Keyword Description Case
sort by Mainly to view data when a certain value arranged in descending order. e.g.: views display_name sort by views
sort by xx descending Mainly to view data when a certain value arranged in descending order. e.g.: views display_name sort by views descending
sort by xx ascending Mainly to view data when a certain value arranged in ascending order. e.g.: views display_name sort by views ascending

11.6 Aggregate Keywords

Table 11-6 Aggregate Keywords

Keyword Description Case
count Mainly to calculate the total number of rows for Measure/Attribute column. e.g.: count name
unique count Mainly to calculate the amount of unique data for Measure/Attribute column. e.g.: unique count age
sum Mainly to calculate the sum of Measure column. e.g.: sum age
max Mainly to calculate the maximum value of Measure column. e.g.: max age
min Mainly to calculate the minimum value of Measure column. e.g.: min age
average Mainly to calculate the average value of Measure column. e.g.: average age
variance Mainly to calculate the variance of Measure column. e.g.: variance age
standard deviation Mainly to calculate the standard deviation of Measure column. e.g.: standard deviation age
between xxx and xxx Mainly to filter values from Measure column in an interval e.g..: Profit between 10 and 100

11.7 Rank Keywords

  • Obtain certain data after sorting the Measure column from large to small (system identification).

  • XX below means values from Measure column, SS means values from Attribute column. If multiple Attribute columns are required, use commas to separate the column names, such as: top 2 sum Sales by Region, Order_Date

  • sum is an aggregation method, which can be replaced with other aggregation keywords. If no aggregation is added, the values are filtered according to the row data in the original table.

Table 11-7 Rank Keywords

Keyword Description Case
top sum XX Mainly to view data with the largest value of XX. e.g.: top sum age
bottom sum XX Mainly to view data with the smallest value of XX. e.g.: bottom sum age
top x(number) sum XX Mainly used to view the largest x data of XX. e.g.: top 5 sum age
bottom x(number) sum XX Mainly used to view the smallest x data of XX. e.g.: bottom 5 sum age
top x(number) to y(number) sum XX Mainly to view data ranked from top x to top y of XX. e.g.: top 2 to 5 sum age
bottom x(number) to y(number) sum XX Mainly to view data ranked from bottom x to bottom y of XX. e.g.: bottom 2 to 5 sum age
top sum XX by SS Mainly to view the top sum of XX group by SS.
(Query data with the largest sum value of XX in each SS column)		 e.g.: top sum Sales by Region
bottom sum XX by SS Mainly to view the bottom sum of XX group by SS.
(Query data with the smallest sum value of XX in each SS column)		 e.g.: bottom sum Sales by Region
top x(number) sum XX by SS Mainly to view the top x sum of XX group by SS.
(Query data with the largest x sum value of XX in each SS column)		 e.g.:
1. top 5 sum Sales by Region
2. top 5 sum Sales by Region,Order_Date
bottom x(number) sum XX by SS Mainly to view the bottom x sum of XX group by SS.
(Query data with the smallest x sum value of XX in each SS column)		 e.g.:
1. bottom 5 sum Sales by Region
2. bottom 5 sum Sales by Region, Order_Date
top x(number) to y
(number) sum XX by SS		 Mainly to view sum of XX ranked from top x to top y group by SS.
(Query data ranked x to y of sum XX in each SS column)		 e.g.: top 2 to 5 sum Sales by Region
bottom x(number) to y(number) sum XX by SS Mainly to view sum of XX ranked from bottom x to bottom y group by SS.
(Query data ranked bottom x to bottom y of sum XX in each SS column)		 e.g.: bottom 2 to 5 sum Sales by Region

11.8 Growth Keywords

  • YY is a Measure column, XX is a Time column. If you need to compare multiple Measure columns at the same time, you can use commas to separate them. For example: growth amount of sum Sales, average Quantity by Order_Date yearly

  • monthly can be changed to yearly, quarterly, weekly or daily + “sum” is an aggregation method and can be replaced with other aggregation keywords. If not added, the default is to aggregate by sum.

  • The default of “growth (amount) of YY by XX” is monthly. For example: growth amount of sum Sales by Order_Date.

Table 11-8 Growth Keywords

Keyword Description Case
growth of YY by XX monthly Mainly to calculate the (month-on-month) growth rate under two conditions.
e.g.: “monthly” calculates the growth rate of current month over the previous month.		 e.g.:
1. growth of Sales by Order_Date monthly
2. growth of Sales, Profit by Order_Date
3. growth of Sales, Profit by Order_Date quarterly
growth of YY by XX monthly year over year Mainly to calculate the (year-on-year) growth rate under two conditions.
e.g.: “monthly” calculates the growth rate of the current month of current year over the same month of last year).		 e.g.:
1. growth of Sales by Order_Date monthly year over year
2. growth of Sales, Profit by Order_Date quarterly year over year
growth amount of YY by XX monthly Mainly to calculate the (month-on-month) growth amount under two conditions.
e.g.: “monthly” calculates the growth amount of current month over the previous month.		 e.g.:
1. growth amount of Sales by Order_Date
2. growth amount of Sales by Order_Date monthly
3. growth amount of Sales, Profit by Order_Date quarterly
growth amount of YY by XX monthly year over year Mainly to calculate the (year-on-year) growth amount under two conditions.
e.g.: “monthly” calculates the growth amount of current month of current year over the same month of last year).		 e.g.: growth amount of Sales by Order_Date monthly year over year

11.9 VS Keywords

  • Multiple “vs” can be used for comparison.

  • When comparing multiple columns under the same comparison condition, you can use commas to separate them. For example: “Office Supplies” vs “Technology” Sales, average Quantity.

Table 11-9 VS Keywords

Keyword Description Case
“mm” vs “nn”, XX “mm” and “nn” are different values in the same column, or both are in time or date format.
Followed by the comparison data column XX.
Note: use quotation marks for mm and nn		 e.g.:
1. “Office Supplies” vs “Technology” Sales
2. “1/1/2020” vs “2/1/2020” Sales
XX vs YY Both XX and YY are part of a time or date keyword. Followed by the comparison data column. e.g.: today vs yesterday Sales, Profit
XX vs all XX is non-time Attribute column. Followed by the comparison data column.
To compare the data in column XX with the sum.		 e.g.:
1. Product_Name vs all Quantity (Compare the sales volume of each product with the total sales volume)
2. Product_Name vs all average Quantity

Updated: