Using table calculations in visualizations
Just like regular dimensions and measures, table calculations are automatically displayed in visualizations.
In addition, you can use table calculations to decide which rows of your data should be displayed in a visualization. The example we’ll use to explore this feature is broken down below, and includes weekly sales information about the Accessories category. Note that the underlying data table includes the dimension Orders Created Week, the measure Total Sale Price, and a table calculation called Percent of Previous Week Sales that compares the revenue of each week against the previous week.
You can prevent certain rows of data from showing up in the column chart. To do so, you’ll create a table calculation that evaluates to true or false, then hide the false values (which will appear as “No” entries in your data table). You don’t want the formula to result in the word “true” or “false”; rather, it should be a condition that is either true or false.
To achieve this, you could create a table calculation, Exceeds Previous Week Sales, that evaluates whether the Percent of Previous Week Sales calculation is greater than 1:
This will result in a table that includes a new table calculation that evaluates each row against the Exceeds Previous Sales calculation, and displays a Yes or a No, depending upon whether the percent of previous is greater than 1.
To hide all the rows where a particular week’s revenue did not exceed the revenue of the previous week, click the gear icon at the top left of the logical calculation and select Hide “No”s from Visualization. The resulting visualization will now display only the weeks that exceeded the previous week’s revenue.
One common use case for this feature is hiding the first or last row from a visualization, since many types of analyses create bad rows at the beginning or end of a table. For example, when you are calculating running totals, have a partial day ending a date analysis, or, like the example below, when you are calculating a percent of the previous row:
To get rid of that row, simply create a new table calculation to filter out the null value by using the
Then, hide the row through the same process as above, selecting Hide “No”s from Visualization.