Which of the following is the name for the values on which a function takes action or performs calculations?
This article describes the types of table calculations available in Tableau and when to use them. It uses simple examples to demonstrate how each calculation transforms data in a table. For more information on how to create and configure table calculations, see Create a table calculation. Show
Difference From calculationA Difference From table calculation computes the difference between the current value and another value in the table for each mark in the visualization. With a Difference From, Percent Difference From, or Percent From calculation, there are always two values to consider: the current value, and the value from which the difference should be calculated. In most cases, you want to calculate the difference between the current value and the previous value, as in the procedure above. But in some cases you may want something different. To specify from which value the difference should be calculated:
ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Difference From table calculation to calculate how sales fluctuate (how much they go up or down) between the years for each month.
You can see that in January, there was a 368 USD difference between sales in 2012 and 2013, and a 26,161 USD difference between sales in 2013 and 2014. Tip: When calculating year-over-year growth, the first year doesn't have a previous year to compare to, so the column is left blank. Hide the column that you don’t want to show to keep the calculation intact. For details, see Hide rows and columns. Why? If you filtered out the first year to remove it from the view, it would also remove it from the calculation so the second year doesn't have a previous year to compare to and is left blank. Instead of filtering, hiding the column keeps the calculation intact. Moving calculationFor each mark in the view, a Moving Calculation table calculation (sometimes referred to as a rolling calculation) determines the value for a mark in the view by performing an aggregation (sum, average, minimum, or maximum) across a specified number of values before and/or after the current value. A moving calculation is typically used to smooth short-term fluctuations in your data so that you can see long-term trends. For example, with securities data there are so many fluctuations every day that it is hard to see the big picture through all the ups and downs. You can use a moving calculation to define a range of values to summarize using an aggregation of your choice. ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Moving calculation to find out how sales totals are trending over time. To do this, you can transform each monthly total so that it averages the monthly total for it and the two previous months over time.
You can see the average sales over time. For example, the value listed for December 2011 is the average sales for October, November, and December, 2011. The value listed for January, 2012 is the average sales for November and December, 2011, and January, 2012. Add Secondary CalculationWith Running Total and Moving Calculation table calculations, you have the option to transform values twice to obtain the result you want—that is, to add a secondary table calculation on top of the primary table calculation. For example, you could add an initial table calculation to calculate the running total for sales per month within each individual year, and then a secondary calculation to calculate the year-over-year percent difference for each month from one year to the next. For an example showing how to create a secondary calculation, see Running Total calculation. Percent Difference From calculationA Percent Difference From table calculation computes the difference between the current value and another value in the table as a percentage for each mark in the visualization. With a Difference From, Percent Difference From, or Percent From calculation, there are always two values to consider: the current value, and the value from which the difference should be calculated. In most cases, you want to calculate the difference between the current value and the previous value, as in the procedure above. But in some cases you may want something different. To specify from which value the difference should be calculated:
ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Percent Difference From table calculation to calculate how sales fluctuate (how much they go up or down) between the years for each month. Values are calculated as percentages.
You can see that between January and February, 2011, there was a -66% difference in sales, but between February and March, 2011, there was a huge improvement of 1,058% sales. Percent From calculationA Percent From table calculation computes a value as a percentage of some other value—typically, as a percentage of the previous value in the table—for each mark in the visualization. With a Difference From, Percent Difference From, or Percent From calculation, there are always two values to consider: the current value, and the value from which the difference should be calculated. In most cases, you want to calculate the difference between the current value and the previous value, as in the procedure above. But in some cases you may want something different. To specify from which value the difference should be calculated:
ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Percent From table calculation to calculate the percentage of a previous value. For example, you can calculate what percentage of sales in January 2011, was made in February 2011.
You can see that February, 2011 made 34% of the sales made in January, 2011; March, 2011 made 1,158% of the sales made in February, and so on. Percent of Total calculationFor each mark in the view, a Percent of Total table calculation computes a value as a percentage of all values in the current partition. ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Percent of Total table calculation to calculate the percentage of total sales each month makes within a quarter. For example, you can see that January, 2011 makes up 18.73% of sales made in Q1.
Or you can calculate the percentage of total sales each month makes within a year. For example, you can see that January, 2011 makes up 2.88% of sales made in 2011.
Percentile calculationFor each mark in the view, a Percentile table calculation computes a percentile rank for each value in a partition. ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Percentile table calculation to rank the total sales for each month in a year as a percentage, rather than a whole number (for example, 1 through 10).
Since February made a very small amount of sales in 2012 compared to the overall total, it is ranked as 0.0% (or number 1 out of 12, since this example is Ascending, and therefore ranked from least to most). Sales in January, 2012 were a bit higher and were therefore ranked as 9.1% (or number 2 out of 12 months). Since November made the most sales in 2012, it is ranked as 100% (or number 12 out of 12). Descending vs. AscendingAscending order ranks values from least to most. Descending order ranks values from most to least. Rank calculationFor each mark in the view, a Rank table calculation computes a ranking for each value in a partition. ExampleConsider the text table below. It shows the total sales per month for 2011, 2012, 2013, and 2014 for a large store chain.
You can use a Rank table calculation to calculate a ranking for each month in a year.
You can see that, since November made the most amount of sales in 2012, it is ranked as number 1 (because the rank is in descending order, meaning it is ordered from most to least). Since February made the least amount of sales in 2012, it is ranked number 12. Descending vs. AscendingAscending order ranks values from least to most. Descending order ranks values from most to least. For Rank table calculation, the default value is Descending. Rank TypeOne issue with Rank calculations is that there may be more than one mark with the same value. What would happen, for example, if Tables in the Central region and Appliances in the South region both had sales of exactly $36,729? Tableau lets you specify how to handle such cases by including an additional field in the Table Calculation dialog box when you set Calculation Type to Rank. The choices are listed below. The number sequence at the beginning of each option show how each option would rank a hypothetical set of four values where two of the values are identical:
Running Total calculationFor each mark in the view, a Running Total table calculation aggregates values cumulatively in a partition. It can do this by summing values, averaging values, or replacing all values with either the lowest or highest actual value. Suppose you are starting with the following text view, which shows sales totals broken out by year (from left to right) and by quarter and month (from top to bottom):
Instead of absolute sales values, you want to see a running total of sales for each year, such that each month’s sales are added to all previous months’ sales. Create the Basic View
You now have the basic view, showing Sales by Order Date over a four-year period, by month, quarter, and year. Add a Running Total table calculation to the basic view
The Running Total doesn’t have to be a sumFor a Running Total table calculation, Tableau can update values cumulatively in other ways than summing. Choose one of the options from the drop-down list just below the Calculation Type field:
Restarting every optionThe Restarting every option is only available when you select Specific Dimensions in the Table Calculations dialog box and when more than one dimension is selected in the field immediately below the Compute Using options—that is, when more than one dimension is defined as an addressing field. This option is not available when you’re defining a table calculation with Compute Using. You can use this setting to set a break (that is, restart of the calculation) in the view, based on a particular dimension. Restarting every can be useful in the following situations:
For example, if you take the result of the Running Total calculation you added above, you can see the effect of Restarting every by doing the following:
Add Secondary CalculationWith Running Total and Moving Calculation table calculations, you have the option to transform values twice to obtain the result you want—that is, to add a secondary table calculation on top of the primary table calculation. For example, you could add an initial table calculation to calculate the running total for sales per month within each individual year, and then a secondary calculation to calculate the year-over-year percent difference for each month from one year to the next. To do this, first add the primary table calculation, as shown above. Then continue as follows:
What is used to perform calculations in a spreadsheet?Excel performs calculations using formulas and functions. A formula is a series of commands instructing Excel to perform calculations based on designated values, cell references, and commands. A function is a pre-written formula.
Which function would you use to calculate the mean of a range of values?The Excel AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria.
What is formula and function?A Formula is an equation designed by a user in Excel, while a Function is a predefined calculation in the spreadsheet application.
What do you call an Excel function that is used to return the value in a given row or column of a specified range?The INDEX function returns a value or the reference to a value from within a table or range.
|