A detailed guide on how to use Calculations in your Queries
Calculations are a useful way of summarising your data. They require two things to work correctly:
- A column to base the calculation off
- A column that the data should be grouped by
There are 5 different calculations types:
A count of...
Count the number of unique values in the specified column
The maximum...
Displays only the highest value in the specified column
The minimum...
Displays only the lowest value in the specified column
The total...
Adds together all the values and displays them as a total amount
The average...
Calculates the average value for a specified column
How to use calculations
Let's look at a simple example of how calculations can be used...
Example: We have a dataset containing some simple sales data. This contains basic information about what was bought by whom and which member of the team managed the sale. Below is a small sample of what the data looks like.
We want to see how many units each owner has sold
To do this, we need to add up all of the values in the Qty column and group them by Owner.
The first thing we need to do is choose our calculation. In this case we need to choose The total...
Then we need to select the column we want our totals to come from. In this case we'll choose the Qty column
Finally, we need to select how the data should be grouped. Because want the total for each Owner, we'll select Owner.
This means our calculation statement should read:
"Show me The total Qty Grouped by Owner"
Adding multiple calculations
You can add as many calculations as you like. All you need to do is click the small plus button next to the calculation.
Adding multiple groups
You can also add multiple groups. This will break down your calculations even further. In the example below, we have grouped our calculations by Owner and Product.
Now, our query shows us the quantity and total cost for each of the different products that an Owner has sold.
Removing Calculations
If you want to remove your calculations simply hover over the 'Show me' text and hit the X. This will remove ALL the calculations you have added. In the future you'll be able to remove individual calculations.
Removing Groups when you have added Calculations
In order for Calculations to work, you have to have grouped your data. You can remove individual groups by clicking the X next to the group. As long as you still have at least one group in your query sentence, your calculations will not be removed.
However, If you remove all of your groups, you calculations will also be removed as a result. To remove all of your groups, hover over 'Grouped by' and click the X. This will reset your query and remove anything that relies on those groups, such as calculations, filters and super columns.
See the Grouping Your Data article for more on how groups work.
Combining calculations with other Query components
The real power of Queries comes when you combine calculations with other Query components.
By using calculations along with additional groups and filters, you are able to display the specific data you need to make better decisions.
Let's look at an example
We have a dataset that tracks our sales pipeline. The dataset includes information such as the sale Owner (account manager), the Status of the sale and a number of Revenue figures for each sale.
Let's say that we want to know what the total value of the highest potential sales each Owner has in their client base, and what the status of those potential sales are.
To create a query to show this data, we'll need:
- A calculation to show the total value of the Potential Sales Revenue
- To Group our data by Owner and Status so we can see the total Potential Sales Revenue for each owner and status separately
- A Filter to remove the low value potential sales, let's say Potential Sales Revenue is less than £600,000
- To Sort our data so the highest Potential Sales Revenue is displayed first
This translates to a Query sentence:
Show me The total Potential sales revenue Grouped by Owner, Status Only where Potential sales revenue_sum is Greater Than 600,000 Sorted by Potential sales revenue_sum (descending).
If you need further guidance on how to use Calculations, contact your account manager.