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 |