Using Ask Me
Sisense Infusion Apps provide a simple natural language querying mechanism to retrieve data from a view. The natural language query (NLQ) for infusion apps is not the same as the NLQ in the Ask Me section of the Sisense dashboard.
In Slack and Microsoft Teams, you can write an NLQ and interact with the bot. Results are returned in a list format.
In Google Workspace, you can write an NLQ, and results are returned in a summary, chart, or table.
In Office 365, writing NLQ will return a raw data set added to your spreadsheet.
Natural language queries are not case sensitive. You can enter your own jargon, such as “show me”, but view names, column names, and literal values must be spelled correctly.
Views
Each query requires you to reference a view. An NLQ can only be based on a view (as opposed to a data model). Before you query data, you must browse through the list of views available to you to ensure that the information you want is encompassed by at least one of those views.
Filtering
Each view will have one or more filter fields enabled for filtering. Fields may be pre-filtered in the view itself; these can be overridden by explicitly specifying filters in the query. You cannot filter by a field which is not exposed in the view as a filter.
Using Operators
Date Literals, Text, and Numerals can be filtered with operators:
Value |
Format |
Example |
Equals or Between |
= |
close date = '2020-10-01 to 2020-12-31' (between Oct 01, 2020 and Dec 31, 2020) |
Greater Than |
> |
close date > '2019-07-22' (date after July 22, 2019) amount > 100000 |
Greater Than or Equal To |
>= |
close date >= '2019-07-22' (date on or after July 22, 2019) amount >= 50000 |
Less Than |
< |
close date < '2019-07-22' (date before July 22, 2019) amount < 100k |
Less Than or Equal To |
<= |
amount <= 10m |
Not Equal To | <> | amount <> 100000 |
!= | amount != 100000 |
Filtering Text Fields
You can filter on a text field by referencing a [column name] and [operator] and one or more [values]. All [values] must be contained by single quotes.
Format |
Example |
<col name> = 'value1,value2,valueN' |
owner = 'sam,matt,john' |
This filter clause will be translated as: owner contains 'sam', or owner contains 'matt', or owner contains 'john'.
Exact Match
Text fields can be filtered by exact text using the equals operator.
Format |
Example |
<column name> equals ‘<literal value>’ |
sales rep equals ‘john smith’ |
Partial Match (Contains)
Text fields can be filtered by partial text using the (contains|has|includes) operators.
Format |
Example |
<column name> contains ‘<literal value>’ |
sales rep contains ‘john’ |
sales rep has ‘john’ | |
sales rep includes ‘smith’ |
Logical OR Filters
You can specify multiple values filtered as logical OR using the comma as a separator.
Format |
Example |
<column name> operator ‘<literal value 1>,<literal value 2>,<literal value N>’ |
sales rep contains ‘john,sam’ |
sales rep equals ‘john smith,sam williams’ |
Note:
Do not leave spaces between the comma separators. The spaces will be included in the search.
Logical AND Filters
You can specify multiple values filtered as logical AND using the + separator.
Format |
Example |
<column name> operator ‘<literal value 1>+<literal value 2>+<literal value N>’ |
description contains ‘health+insurance’ |
Note:
Do not leave spaces between the + separators. The spaces will be included in the search.
Filtering Numeric Fields
You can filter a numeric field using supported operators, including standard arithmetic operators (see Using Ask Me above). Numeric notations for K (thousand), M (million), and B (billion) are supported.
Note:
Do not include currency symbols or comma notations when specifying numeric values.
Filtering Date Fields
You can define a date range within your query to narrow down the result set. These ranges can be defined as a literal or dynamic time window. To filter on a specific date range, include the specified range immediately after the view name.
The basic syntax for referencing a date field is:
<column name> = “range or literal”
Dynamic Date
Queries with a dynamic date do not require a manual filter each time new data is available. Result sets will change relative to the dynamic date. You do not need to use an operator when defining a dynamic date.
The following range components are supported: This, Last, Next, is Before, is After.
The following date elements are supported: Year, Quarter, Month, Today, Yesterday.
The components are not case sensitive.
Value |
Example |
Days |
Today Yesterday 2 Days ago |
Months |
This month Last month Last 3 months |
Quarters |
This and Last Quarter |
Years |
This and Next 2 Years |
Literal Date
A literal date is a fixed expression that represents an individual day/month/quarter/year or a range of time. You can use single or double quotes. Multiple dates can be filtered with a comma separated list of dates.
Value |
Format |
Example |
Individual Days |
yyyy-MM-dd MM/dd/yyyy |
close date = '2020-10-01’ Order Date = ‘07/27/2022’ |
Date Range | <column name> = “date1 to date2” |
Order Date = ‘02/21/2022 to 05/05/2022’ |
Months |
MMM yyyy |
close date = 'Feb 2020' |
Quarters |
Q yyyy yyyy Q |
Order Date = “Q3 2019” Order Date = “2020 Q1,2020 Q2” |
Years |
yyyy |
Order Date = “2020” close date = '2019,2020' |
Default Date Reference
In order to enhance the natural language experience it is possible to simply add the date filter condition immediately after the view name itself. This will effectively filter the date field flagged as the default date.
Default date filtering is performed using the following syntax:
- <view name> <date range> ….
- <view name> in <date literal>
For example, consider a view named Orders. The following filtering could be performed:
- show orders last year
- display orders this and last 2 years
- list orders in 2022
- show orders in Aug 2019
- show orders in Q2 2020
Using Analyze
Note:
Analyze is only supported for Slack and Microsoft Teams.
Append the word Analyze to any query, and the bot will summarize metrics and provide a Top 10 breakdown of the different dimensions. The analysis performed is based on the following metrics:
- The Distinct Count of the Unique Identifier as specified in the view.
- Past Period and YoY Differential if there is a date filter defined in the query.
- Aggregation of the Default measure as specified in the view.
- Range of Min and Max.
- Past Period and YoY Differential if there is a date filter defined in the query.
- Aggregation of all other measures.
To analyze data:
- Run a query.
- Click Analyze Data on a result set or prefix a query with the word "analyze".
- Select a dimension to analyze the data further.
Note:
Selecting a time-based dimension will display a timeseries (line) chart.
Examples
Below are some examples showing the different kinds of queries you can write for the view “orders”.
To find orders greater than 10,000 placed between Feb 21, 2020 and Sep 27, 2020:
- what are the orders whose amount > 10k and invoice date = '2020-02-21 to 2020-09-27'
To find orders placed last month by customers in logistics or healthcare:
- get orders with invoice date = 'last month' and whose industry = 'logis,health'
To find Sam and Oliver’s orders for the current month:
- show me orders this month whose sales rep = ‘sam,oliver’
To find Sam and Oliver’s orders for the current month that are over $100,00:
- display orders this month whose sales rep = ‘sam.oliver’ and amount > 100k
.r.