Define a query's fields and calculations

Define query fields

To add specific columns or full sources to the query results, select them on the Sources panel, click Add to Fields or move them to the Fields tab.

After you add a column to the query, you can select it on the Fields tab to view its source, ID, and data type from the Field Properties right panel. From this panel, you can also adjust how a field's name or data appears in the query results.

1. To clarify the field's data, under Header, enter its name to appear in the query results. For example, if the query includes multiple fields with the same name from different sources, update their names for additional context.
2. To return a different data type in the results, such as to convert numerical Text data to a Decimal or Integer, under Cast Type, select the data type to use.

   Note: Tag values are saved as strings. To use a tag as another data type, select the desired type in Cast Type.

3. To adjust how dates appear in a Date or Timestamp result, under Function, select the abbreviation—such as Day or Quarter—to display; or, for a Custom Format, enter the expression of its new format of its values, such as %Y-%m for YYYY-mm:

   Expression	Displays
   %b	The abbreviated month name, such as Jan or Dec
   %M	The month's full name, such as January or December
   %c	The month's numerical value as a single or double digit, such as 1 for January, or 12 for December
   %m	The month's numerical value as a double digit, such as 01 for January
   %D	The day of the month with an English suffix, such as 1st or 23rd
   %Y	The full year as four digits
   %y	The abbreviated year as two digits

   Note: By default, dates or timestamps appear in the ISO 8601 format.

4. Under Aggregation Type, select whether to return a calculation—or aggregation—of the field's values in the results. For example, a field can return a count of its records or an average or sum of its amounts. To provide a calculation, select the aggregation to perform:

   Aggregation	Field or Cast Type	Result
   Sum of	Number	The total of the field's values
   Count of	All	The total number of records with a value in the field
   Max of	Integer, Number, Date, Timestamp	The field's largest or latest value
   Min of	Integer, Number, Date, Timestamp	The field's smallest or earliest value
   Average of	Integer, Number	The average of the field's values

   Note: To include only records with unique values in the calculation, select Use distinct aggregation.

5. Click Apply.

Define calculations

With a calculation, you can automatically apply a formula to multiple source columns, using an expression that includes:

• Operators, such as {1} + {2} to calculate the sum of columns' values
• Functions, such as concat({1}, {2}) to generate a string of columns' values

1. Move Calculation from the Sources panel to the query, and then select it in the query.
   
2. From the Properties panel, under Header, enter how the calculation's name should appear in the query results.
   

   Note: To set the calculation's alias, enter it as the header. Calculations don't support syntax that contains AS to set an alias.

3. Move the columns to include in the calculation from the Sources panel to under Included Columns on the Properties panel.

   Tip: To include a column in a calculation, you can also select the calculation in the query, and then select Add to Calculation from the column's right-click menu in the Sources panel.

4. Under Calculation, enter the formula or expression to calculate, using operators and functions and the included columns represented as their assigned numerical token, such as {1} + {2}.
   • To quickly add a column's numerical token to the calculation, click its name under Included Columns.
   • To quickly add the syntax for a function or operator, select it from the Calculation menu.
   • To reference a parameter value in the calculation, select the parameter from the Parameters menu, or enter its name after a colon, such as :ReportingPeriod.

     Note: To add a parameter, select Create new parameter from the Parameters menu.

5. Click Apply.

Arrange query fields and calculations

To define how fields and calculations appear in the query results, arrange them in the order in which they should appear. To move a query field or calculations on the Fields tab, drag it to its appropriate position, or right-click on it and select whether to move it up, down, or to the top or bottom of the fields.

Duplicate a query field or calculation

To create a copy of a query field or calculation, such as to use with a different aggregation or formula:

1. On the Fields tab of the query, right-click on the field or calculation to copy, and select Duplicate Field.
2. Select the duplicate—with a header of [copied_fieldname]_copy—and update its header and any additional properties on the Field Properties right panel.
3. Click Save .

Remove a query field or calculation

To remove a field or calculation from a query, on the Fields tab, click its Delete , or right-click on it and select Remove Field.