This has been calculated on each row in the underlying data. The final column – profit ratio (NA) – is a non-aggregate calculation. The primary difference between aggregate and non-aggregate calculations is that aggregate calculations often can’t be sensibly calculated for each row in the underlying data set – it normally only makes sense to calculate them when the data is aggregated.Ĭonsider the profit ratio example, based on the following data: Therefore an example of an aggregate calculation would be:īut what is special about aggregate calculations? Examples of aggregate functions are SUM, AVG, MAX & MIN (there are a few others). If your data source changes regularly… suppose you receive a new excel file every day for example, consider adding your calculations to Tableau so you don’t have to recreate them in the underlying dataĪggregate calculations are those that use aggregate functions. If your data source is shared by many users… consider creating calculations in the underlying data so your team can all benefit from them Exactly where you should create them depends on your specific circumstances…
To help with your understanding of non-aggregate calculations, it is useful to know that these type of calculations can be created EITHER in your underlying data (say Excel or SQL Server) OR in Tableau. In Tableau – Making direct reference to the fields by name In Excel =(A2-B2) Making reference to the data by column ID In summary, a non-aggregate calculation will appear roughly as below in either Tableau and Excel: The result of this calculation would be provided for every row in the underlying data. In Tableau calculated fields are added in the same way, regardless of whether you are adding a non-aggregate or aggregate calculation – but it is easy to tell which you are creating! If you’re not using aggregate functions, then you’re creating a non-aggregate field.Ī non-aggregate calculation in Tableau is defined as a calculated field which does not use any functions from the ‘Aggregation’ function group.įor example – – would be a non-aggregated calculation. Get started by right clicking in the data window, or by using the pull down menu at the top of the data window shown in the image. =LEFT(C2,1) where column C contains the product nameĬalculated fields are added in much the same way in Tableau.
The resulting column would be titled ‘Profit’.Įxample 2: Determining the first letter of a product name =(A2-B2) where column A contains the sales amount and column B the cost.
In Excel, non-aggregate calculations are created in the worksheet which contains the raw data – typically be creating a new column and adding a formula such as:Įxample 1: Calculating profit by subtracting cost amount from sales amount Non-aggregate calculations are performed for each row in the underlying data, rather than being performed on aggregated data (such as you would find in a pivot table or Tableau view). These are the simplest type of calculation, and without doubt the most commonly used. Table calculations – these will be dealt with in a later post Aggregate calculations – calculations which are ONLY meaningful after the data has been aggregatedģ. Non-aggregate calculations – calculations which are meaningful on each row of the underlying dataĢ.
There are three major families of calculated fields in Tableau, and each has an equivalent in Excel – only the first two are dealt with in this post.ġ.
#Tableau desktop 9.1 download series
This is the second part of a series of posts, see part 1 here.Ĭalculated fields in Tableau very similar to Excel formula, so if you have a reasonable understand of how Excel formula are used, transitioning this knowledge to Tableau can be straightforward. | Tom Brown Tableau for Excel users – Part 2 – Calculated fields
0 kommentar(er)
