🕐 8 min read
In the VizQL Data Service Postman collection, you can either duplicate the example queries and change them, or modify the example queries directly. Then, you can type in the request body to change your query.
A VizQL Data Service query is a JSON object. It contains three fundamental components.
returnFormat: (string) Determines whether the return format is human readable (OBJECTS) or compact (ARRAYS).debug (Boolean) Returns more detailed error messages from VDS. The default value is false.disaggregate (Boolean) Determines the level of granularity in the response. The default value is false.Here are several examples based on the Superstore data source. Find more examples in the Postman Collection.
The following is a query to return the sum of sales by segment. See the query schema under Function for the list of available functions.
{
"columns": [
{
"columnName": "Segment"
},
{
"columnName": "Sales",
"function": "SUM"
}
]
}
This next example is a query to return the sum of sales by segment, rounded to the nearest dollar.
{
"columns": [
{
"columnName": "Segment"
},
{
"columnName": "Sales",
"function": "SUM",
"maxDecimalPlaces": 0
}
]
}
The following example is a query to show the profit margin by category subcategory sorted in descending order of profit margin. Shows the most profitable category/sub-category of items for sale.
{
"columns": [
{
"columnName": "Category"
},
{
"columnName": "Sub-Category"
},
{
"columnName": "Profit Margin",
"calculation": "SUM([Profit])/SUM([Sales])",
"sortPriority": 1,
"sortDirection": "DESC"
}
]
}
This next example is a query to show the AOV by year and quarter. Sorting is accomplished by providing the sortPriority field to a column. Sorting on multiple columns is supported. The column with the highest sort priority (meaning the lowest number) is sorted first, followed by subsequent columns. The default sort direction is in ascending order.
{
"columns": [
{
"columnName": "Order Date",
"function": "DATE_YEAR",
"sortPriority": 1
},
{
"columnName": "Order Date",
"function": "DATE_QTR",
"sortPriority": 2
},
{
"columnName": "AOV",
"calculation": "SUM([Profit])/COUNTD([Order ID])"
}
]
}
Next up is a query to show sales by sub-category from highest to lowest (descending order). This example includes a filter to remove individual sales below 200 K.
{
"columns": [
{
"columnName": "Sub-Category"
},
{
"columnName": "Sales",
"function": "SUM",
"sortPriority": 1,
"sortDirection": "DESC"
}
],
"filters": [
{
"columnName": "Sales",
"filterType": "QUANTITATIVE",
"quantitativeFilterType": "MIN",
"min": 200000
}
]
}
A column can be constructed in one of three ways:
A column from the data source: Use columnName to reference a column directly from the data source.
{
"columnName": "Category",
}
An aggregated column from the data source: Use the column sub object and include the columnName and function to specify an aggregated column.
{
"column": {
"columnName": "Sales",
"function": "SUM"
}
}
A calculation
{
"column": {
"columnName": "Profit Margin"
"calculation": "SUM([Profit])/SUM([Sales])"
}
}
The following is a complete list of things you can add to your column object. (See the Column Object in the schema.)
columnName: (required) The name of the column. Either a reference to a specific column in the data source, or in the case of a calculation a user supplied name for the calculation.columnAlias: An alternate name to give the column. This will only be used in Object format output.function: Provide a function for a measure to generate an aggregation against that column’s values. For example, providing the SUM function causes an aggregated SUM to be calculated for that column. A column can’t contain both a function and a calculation. For more information, see QueryDatasource.calculation: Provide a calculation to generate a new data column based on that calculation. The calculation should contain a string based on the Tableau Calculated Field Syntax. Since this is a newly generated Column, you must give it its own unique Column Name. A column can’t contain both a function and a calculation.maxDecimalPlaces: The maximum number of decimal places. Any trailing 0s will be dropped. The maxDecimalPlaces` value must be greater or equal to 0.sortDirection: The direction of the sort, either ascending or descending. This object requires a set value for sortPriority.sortPriority: To enable sorting on a specific column, provide a sortPriority for that column, and that column will be sorted. The sortPriority provides a ranking of how to sort columns when multiple columns are being sorted. The highest priority (lowest number) column is sorted first. If only one column is being sorted, then any value may be used for sortPriority. The sortPriority value should be an integer greater than 0.A filter must have the following fields:
There are more requirements, depending on the filter type, as detailed in the following sections.
See Filter Object in the schema. Specifying the Column to filter on.
A Filter always references a single column of data to filter on. See How to Construct a Column above for details on how to specify your column.
There are four kinds of filters you can add to your query. For each filter, you must set filterType, which could be QUANTITATIVE, SET, DATE, or TOP.
This filter is used for measures or dates. You can use it to set a minimum (MIN) value, a maximum (MAX) value, a range of values (RANGE), or a “special” type (SPECIAL). Some rules for this type of filter based on quantitativeFilterType:
quantitativeFilterIncludedValues `“ALL”, “NON_NULL”, “NULL”, “IN_RANGE”, “IN_RANGE_OR_NULL”, “NONE”
There are two different ways you can use Quantitative Filters:
For measures, use the “min” and “max” values to specify a MIN, MAX, or RANGE quantitative filter. These values are raw integers. For example, the following filter is of type RANGE and specifies a min and max number: {
"column": {
"columnName": "Sales",
"function": "SUM"
},
"filterType": "QUANTITATIVE",
"quantitativeFilterType": "RANGE",
"min": 266839,
"max": 1149562
}
For dates, use the minDate and maxDate values to specify a MIN, MAX, or RANGE quantitative filter. These values are Date objects, for which you must specify a “day”, “month”, and “year”. For example, the following filter is of type MIN and uses the date object type:
{
"columnName": "Order Date",
"filterType": "QUANTITATIVE",
"quantitativeFilterType": "MIN",
"minDate": {
"days": 1,
"months": 1,
"years": 2022
}
}
This filter is used for dimensions or dates. You can use it to either include or exclude certain values. You must set the boolean exclude and provide a list of values to either exclude (when exclude=true) or include (when exclude=false).
{
"columnName": "Ship Mode",
"filterType": "SET",
"exclude": true,
"values": ["First Class"]
}
This filter is used for setting a range of dates relative to an anchor. You can set the anchor by passing in an object that has the numeric day (DD), the numeric month (MM), and the numeric year (YYYY). If you set NO anchor, today’s date is used by default.
You must also set the variables units, pastCount and futureCount. These are integers that specify the range.
units can be one of the following values: “DAYS”, “WEEKS”, “MONTHS”, “QUARTERS”, “YEARS”.pastCount is an integer, negative or positive, that specifies how many units you want to start AWAY from the anchor. Use 0 to start from the anchor. For example:
units is “DAYS”, your anchor is today, and your pastCount is -1, that means your lower limit is yesterday.units is “YEARS”, your anchor is 3/15/2023, and your pastCount is -2, that means your lower limit is 2022.units is “WEEKS”, your anchor is today, and your pastCount is 0, that means your lower limit is this week.futureCount works the same way. It’s an integer, negative or positive, that specifies how many units of units you want to end from AWAY from the anchor. Use 0 to end at the anchor. For example:
units is “DAYS”, your anchor is today, and your futureCount is 1, that means your upper limit will be tomorrow.units is “YEARS”, your anchor is 3/15/2023, and your futureCount is 2, that means your upper limit is 2026.units is “WEEKS”, your anchor is today, and your futureCount is 0, that means your upper limit is this week.Generally, pastCount is probably negative and futureCount is probably positive, but you don’t need to follow that convention. However, pastCount MUST be less than futureCount, or you’ll get 0 results.
{
"filterType": "DATE",
"columnName": "Order Date",
"units": "DAYS",
"pastCount": 0, // Start from 1/1/2021
"futureCount": 5, // End at 1/5/2021
"anchor": {
"day": 1,
"month": 1,
"year": 2021
}
}
A top N filter, or filterType: "TOP" allows you to find the top or bottom N results of a given category. You must specify the following inputs:
column or columnName : The same as other queries, this is the column on which you want to filter.fieldToMeasure: this is a filter column on which you are finding the top or bottom results of. See above section Specifying the Column to filter on direction: either “TOP” or “BOTTOM” to show the highest or lowest results.howMany: an integer, how many results you would like to see.The following is an example of a top N Filter to show the top 10 states by sales:
{
"columnName": "State/Province",
"filterType": "TOP",
"howMany": 10,
"fieldToMeasure": {
"columnName": "Profit",
"function": "SUM"
},
"direction": "TOP"
}