Add missing column after Pivot
If your result set is missing a value you'd expect to see from a column, that value won't be visible when you use a Pivot Action. Here's how you can always include all the distinct values in a pivoted column.
Grouped columns are added to the Query Sort in the order they're added to the Query. Aggregated columns must be added manually. You could also use a Sort Rows Action to re-sort when everything is evaluated.
Calculate a percentage
To calculate a percentage, create two queries and merge them with a Cross Join before defining the calculation for the percentage in a new column.
Calculate a rolling sum
A rolling sum can be calculated in the Pipeline by using the Moving Average guided formula and multiplying it by the number of trailing rows.
Calculate a running total
Use either the Running Total built-in autocomplete formula or a SQLite window function in a Custom formula to calculate the running total for a column.
Calculate row number
Use the Visual SQL pipeline to add row numbers to data with a rowid column.
Change a data type
Use cast() or divide() in a Formula Column or Apply Formula Action to change a column's data type.
Combine the contents of your columns by customizing how they'll be combined using Custom Formulas.
Compare time periods
To compare different periods of time, you can use the Visual SQL Pipeline to find the difference between the periods and visualize it on a Single Value chart.
Count distinct totals and percentages
Show the totals row in your Table chart and add a Formula Column to apply the Ratio of Total to each row, which gives the percentage each row contributes to the total column's aggregation.
Count keywords and characters in strings
Use Custom Formulas and other Actions to count the number of rows a keyword appears in a column or count the number of characters in a string.
Dynamic categorical columns
In order to dynamically change between categorical columns on dashboards, use the Visual SQL Pipeline to define Dashboard Controls and the effect they have on your chart.
Extract parts of a string
In order to separate two parts of a string, use a Formula Column with a Custom formula to extract information from the initial column into another column.
Extract values from a JSON string
Learn how to use the Visual SQL Pipeline to extract values from JSON strings.
Filter Action examples
To compare relationships between columns, use a Filter Action in the Pipeline to organize your data based on conditions.
Filter by complete days
You can filter your charts by full days using Relative Date Variables in Visual Mode or editing the query in SQL Mode
Find the date difference between rows
To find the date difference between rows, add a Formula Column and use the 'date difference' formula.
Generate a time series
Generate a time series in Visual SQL's SQL Mode using functions supported by your particular data source
Group & Aggregate example
Using a Group & Aggregate Action in Visual SQL, you can quickly aggregate rows, like the monthly average of users created per day.
Group email addresses by domain
To group email addresses by their domain, extract the domain and then group by domain in the Visual SQL Pipeline.
Include NULL values when applying functions
As functions can't be performed on columns with NULL values, use a Custom formula to convert NULL values to 0 or another column's result.
Omit data using an anti-join
To exclude a large number of rows from your results without using filters, use an anti-join in the Visual SQL Pipeline.
Round numbers in your chart by adding or editing a column in the Pipeline and using SQLite functions to write a Custom formula to round.
SQLite date and time functions
Using the correct SQL syntax and our built-in functions, you can use date modifiers to change the format, filter, or perform calculations with them.
SQLite string functions
SQLite is used in the Visual SQL Pipeline, so you can use the SQLite functions to analyze your data.
Here are our answers to some common questions we've received about the differences between our old Data Explorer interface and our new Visual SQL interface.
Use CASE statements
Use a CASE statement in your Visual SQL chart queries, Dashboard Controls, or in the Pipeline to modify your query results or set conditional logic.