Building point-and-click queries

Selecting fields

Dimensions and measures are divided into separate sections in the field picker, each nested under individual tables in your data warehouse.

• A dimension is a grouping in your query (date, location, type, etc).
• A measure is an aggregation over your dimensions (sum, count, average, etc). For example, to query total revenue per day you would select a date dimension (order_items.created_date) and a revenue measure (order_items.sum_revenue).

Here’s an example of what that would look like:

There are several advanced actions available on field selection using right click, or hovering over the field and clicking the icon.

Identifying workbook-only fields

Fields created in a workbook — such as custom fields and calculated fields — are not part of the shared model. To help you distinguish these from shared model fields, JustAsk displays a small yellow badge called a field verification indicator next to their data type icon:

These are field verification indicators, intended to help you understand which fields in the workbook are from the shared model and which are fields created specifically in the workbook. Fields without an indicator are from the shared model.

When the Verification setting is enabled, verification indicators appear in several locations throughout workbooks:

• Field browser - Next to unverified fields when selecting fields to add to your query
• Data table column headers - In the column headers of your results table
• Visualization configurations - Within the series editor, table configuration, and available fields lists

Searching fields

Fields can be searched with the input box above the field picker. Default search will look at fields and views. However, you can specify additional conditions using these filter options:

• In-use - Limits results based on field usage. By default, all fields display regardless of whether they’re used in the query. Click the dropdown arrow to choose between:
  • Fields in-use - Shows only fields that are selected or filtered in the current query
  • Views in-use - Shows all fields from views that have any in-use fields, useful for discovering related fields in the same view
• Field type - Limits the results to selected data types, such as boolean, number, etc.)
• Topic - Limits the results to fields in selected topics
• Label - Limits results to fields with or without labels
• Descriptions - Limits results to fields with or without descriptions

Additional operators

The search box also supports operators that allow for additional querying options. For example, to limit the field type to date fields, you’d enter data_type:date.

Operator	Description	Example
data_type:	Filters fields based on data type: date, string, boolean, interval, array, json, number	data_type:date
is:	Filters fields based on whether the field is a dimension, measure, or hidden	is:dimension
model_type:	Filters fields based on the model type: schema, shared, workbook, query	model_type:schema
schema:	Filters fields based the schema that contains them	schema:marketing
tag:	Filters fields based on the provided tag	tag:secure

Filtering

Each dimension can be added as a filter to focus on specific rows of data. See Filter queries for more information.

Pivoting

Quickly build pivot tables by right-clicking any field in the field picker and selecting the Pivot option.

Flattening and filtered measures

You can also flatten the pivot and create filtered measures by clicking the actions menu (three dots) on a column and selecting Flatten pivot. When using this feature you can also choose to create an Other bucket on the fly:

The new filtered measures will appear under the Tab-only fields section and the field that was originally pivoted on will no longer be selected as a field that is in-use.

Creating custom fields

Custom fields can be created through the right click menu or using the + button in the field picker. Refer to the Custom fields guide for more information.

Applying quick aggregates and timeframes

JustAsk provides a number of aggregations you can apply to fields right from the UI. In the field picker, right click on a field and select Aggregates. Date fields will have an additional Timeframes option.

Field type	Dimensions (Timeframes)	Measures (Aggregates)
Dates	Date Week Month Year Second Minute Hour Hour of Day Day of Week Name Day of Week Number Day of Month Month Name Month Number Quarter Quarter of Year	Min Max Count Distinct List
Strings	None	Count Distinct List
Numbers	None	Count Distinct Sum Average Min Max List
Booleans	None	Count True Count False

Limiting and paginating rows in results

By default, queries are filtered to 1000 rows of data, but the limit can be adjusted dynamically up to 50,000 rows and paginated to see all result rows directly in JustAsk.

Row limits don’t impact the rows processed, only the rows available in the JustAsk UI. This setting is also used to paginate the results table. For example, if set to 5, the results table will display five rows at a time.

Caution

Use caution with high row limits. Browsers may have limitations on the amount of data that can be shown at once.

Column and pivot limits

JustAsk currently supports any number of measures in the results table. Pivots are limited to 200 columns.

Sorting

Data in the results table can be sorted on a column by column basis, with a cycle through descending, ascending, and unsorted. For sorting over more than one field (secondary sort or double sort), simply sort the first field and hold down shift for subsequent sort actions.

Interacting with tables

Workbook results tables support spreadsheet-style selection, copying, editing, and cell interaction.

Note

All gestures require the visualization to be in table view. These gestures are not available when viewing a chart.

Selecting cells

• Click a cell to select it
• Click and drag across cells to select a rectangular region
• Shift+click a cell to select a range from the anchor to the clicked cell
• Command+click (Mac) or Ctrl+click (Windows) a cell to add or remove it from your selection
• Click a row number to select an entire row. Combine with Shift to extend the selection or Command/Ctrl to toggle the row.
• Click a column header to select an entire column. Combine with Shift to extend the selection or Command/Ctrl to toggle the column.
• Arrow keys to move the focused cell. Hold Shift to extend the selection.
• Command+A (Mac) or Ctrl+A (Windows) to select all cells
• Escape or click outside the table to clear the selection

Copying cells

Select one or more cells, then press Command+C (Mac) or Ctrl+C (Windows) to copy the selected values to your clipboard. Copied data uses a tab-separated format, so pasting into Excel or Google Sheets automatically places values into the correct cells.

When all cells are selected with Command+A / Ctrl+A, column headers are included in the copied output.

Editing cells

• Press Enter on a calculation cell to enter edit mode, or on other cells to expand the cell value
• Double-click a calculation cell to enter edit mode
• Right-click a cell to open the context menu with options to drill, filter, copy, or expand

Reordering columns

Drag a column header to reorder columns. Column reordering is not available in pivot tables.

Editing calculations

When editing a calculation in a table cell:

• Press Enter to save the calculation
• Press Escape to cancel and exit the editor
• Shift+click a cell to insert a cell range reference into the formula

Dashboard tables are read-only. For dashboard table interactions, see Interacting with table tiles.

Formatting number and date columns

Note

The formatting menu described in this section is supported for number and date columns. For unsupported data types, the formatting menu will be disabled in the format bar.

You can quickly format columns in your results table using the quick formatting menu. This menu provides curated format presets based on the selected field’s type, making it easy to apply common number and date formats.

To access the formatting menu:

1. In the results table, click a column header to select the column
2. Click the icon in the format bar above the table

The menu displays format options appropriate for the selected field type.

Number field formats

For number fields, the format menu includes:

• Automatic - Uses the field’s default format
• Number - Standard number format
• Percent - Percentage format
• Currency/Accounting/Financial - Currency formats matched to your locale (for example, UK locale shows GBP options)

Date field formats

For date fields, the format menu shows curated formats based on the field’s date grain:

• DATE and WEEK fields - Day-level formats such as Mar 17, 2022, 2022-03-17, and Relative (3 days ago)
• MONTH fields - Month-level formats such as March 2022, Mar 2022, and 2022-03
• QUARTER fields - Quarter-level formats such as Q1 2022

All date format options include Automatic to use the field’s default format.

Advanced date formatting

The format menu provides quick access to commonly used formats. For advanced date formatting options, open the Edit field panel by:

• Using the column header in the results table - Hover over the header, click the icon, then click Modeling > Edit.
• Using the field browser - Hover over the field, click the icon, then Edit.

Results table options

Clicking the Options button while viewing a workbook’s results displays several options, outlined below.

Column width

This setting allows you to automatically or manually set column width.

When widths are set automatically, JustAsk measures columns using the initially visible rows and locks the widths in place so columns don’t shift as you scroll. Widths recalculate when the results or columns change or the table is resized.

Header text

Controls how column headers in the results table are displayed - clipped (truncated) or wrapped to the next line.

View names

When enabled, the name of the view the column belongs to will display above the column header

Column totals

Enabling column totals adds a Total row at the bottom of the table. From here, you can right click on a measure’s total and select Total calculation to customize how the total is calculated.

For example, you have a table that shows total sales by month for 2024. If you wanted to see the average sales across all months, you could change the Total calculation to Average:

Dimension column totals

By default, dimensions - such as dates or IDs - won’t have a Total value. However, you can still apply one by clicking the column total and using the Total calculation menu.

Calculation column totals

Columns with calculations applied can also have custom totals. When column totals are enabled, JustAsk will default to using the column’s calculation as the initial total. Take the following table as an example, where the AVG Item Price and CONCAT Average Item Text columns use Excel-like formulas:

Date	Order Total	Order Items	AVG Item Price	CONCAT Average Item Text
Jan 2024	$50.00	2	$25.00	Average item price is $25.00
Feb 2024	$100.00	3	$33.33	Average item price is $33.33
Mar 2024	$72.00	6	$12.00	Average item price is $12.00
Totals	$222.00	11	$23.44	Average item price is $23.44

In this example:

• The AVG Item Price column has a formula of Order Total / Order Items. Using this formula, the column total is $23.44, which is the average of all the values in the column.
• The CONCAT Average Item Text column has a formula of CONCAT("Average item price is ", AVG Item Price). Using this formula, the column total value is Average item price is $23.44, which uses the column total for the AVG Item Price column.

Row totals

Note

Row totals are only available for queries with at least one pivot.

Enabling row totals adds a Total column to the right of the table. For example, you have products that are sold in the UK and USA. You want to view the total number of returned orders for each country by month, but you also want to calculate the total returns for the month. Creating a pivot table like the following and enabling Row totals will add a column containing this data:

Swap rows and columns (transpose)

Enabling Swap rows and columns transposes the rows and columns in the results table, which is useful when measures are needed across rows. Note: Some features, such as column sort, must be set prior to enabling this feature.

Group measures in pivot

Applicable to pivot tables. When enabled, measures in a pivot table will be grouped together. Let’s say you have the following table, which is pivoted using Status:

Status	Processing	Processing	Completed	Processing
	Total Sales	Order Items	Total Sales	Order Items
	44,562.32	564	100,892.61	2,654

When Group Measures in Pivot is enabled, the table becomes:

Status	Processing		Completed
	Total Sales	Order Items	Total Sales	Order Items
	44,562.32	564	100,892.61	2,654

Downloading results

Tab results can be downloaded as a CSV, Excel file, or JSON. Click Tab > Download or the icon to the left of the Limits menu to get started.

When configuring a download, you can set the following options:

• Format - Select the file format for the download: CSV, XLSX, or JSON
• Apply data formatting - Preserve number and date formatting in the download
• Timezone override - Override the query timezone for the download. The picker pre-populates from your current page selection if the timezone picker is enabled.
• Row limit - Define the number of rows to include in the download. Options vary depending on the selected Format:
  • Current limit - Apply the query’s current row limit to the downloaded results
  • All possible results - Include all possible rows in the result, disregarding the query’s current row limit. For XLSX formats, this option allows downloads of up to 1 million rows.
  • Custom - Specify a custom row limit for the download, which can be greater than the query’s current row limit

Note

If your results include column totals or row totals, CSV and XLSX downloads label one dimension cell in each subtotal or grand total row with Total, matching what’s shown in the results table. JSON downloads don’t include this label.

Removing fields and filters

Fields can be removed from the table either using the field picker (clicking on active fields will remove them) or right-clicking / kebab-icon-clicking in the column’s table header.

Period-over-period analysis

Period-over-period (PoP) analysis is a business intelligence technique that compares metrics in a recent period to the same period in the past.

You can seamlessly create those time periods from the field picker by selecting ”Period over Period” from the three dot menu of the date dimension you want to use.

Above the results pane two drop downs will appear. In the first drop down, choose the current period and in the second drop down, the period you want to compare. The options to compare will default to relevant time frames; however you can always customize that logic.

Users will be able to see more exact period parameters in the information icon on the results table.

Filling missing data

Dimension filling

Often when querying with filters, you may return a result set that does not include every possible dimension, for example count of sales on days with zero sales. It can be valuable to return these missing dimensions for reporting.

After querying a dimension that can be filled (ranged dates, lists), simply right click on the dimension and select ‘Fill in Missing Rows’. Note the option may not always appear on dates when JustAsk does not know the start and end date. To bring the ‘Fill in Missing Rows’ option, simply filter the date over a clear time period (ie. 2023-01-01 to 2023-02-01), which JustAsk will then fill in rows for.

In addition to dates, JustAsk can fill in missing values for enumeration fields - for now, just bins and groups defined in the data model. This may expand over time.

Fill is capped at 8,000 values.

Pivoted table filling

In addition to filling dimensions, it’s often valuable to fill a pivoted table that contains nulls for ‘more correct’ reporting of aggregates. To replace nulls with 0s in the pivottable, simply select “Fill in Missing Cells in Grid” on a pivoted report:

Note

It is best to use Fill in Missing Cells in Grid when adding calculations to your pivoted results. Based on the way that pivoted tables are constructed calculation formulas operating over a range or conditionally (e.g SUMIFS) in particular may behave unexpectedly

Adding controls

Controls allow a user to swap out one field for another on a query making it more dynamic. There are three main types of controls:

• Time
• Field
• Parent

Time controls

These controls will allow you to swap the time frame within the underlying query. This provides an easy UI based mechanism to swap from daily to weekly to monthly and back again, all without any underlying work needing to be done to facilitate it from an administrative perspective. A time frame control can be added from either the field picker or from the date field in the results table by choosing Add Time Frame Selector from the three dot menu.

Field

In order to create a control for dimensions or measures, first add one to the query and switch to the Results view. From there, select the three dot menu on the column and Add field selector. A control will appear above the table with a three dot menu for a user to edit the control (change the label, add additional fields, or add a parent control).

Once there are two or more options in the control, a user can select the field they want represented in the table or visualization and dynamically toggle among the options.

Parent controls

These controls will allow you to coordinate the toggling of multiple controls at once from a single point and click UI. From any existing control, select the option Add parent control. A control will appear above the table, select the three dot menu to edit the control. First add options that will appear in the drop down, then assign the child controls that will be chosen based on the option selected in the parent control.

Note

Controls created in the workbook will not appear on the dashboard. Read more about how to add controls on the dashboard level here.