Don’t Show Me the Numbers! Play Suppression Whack-A-Mole in Tableau
As analysts, we all love our numbers. We clean them, analyze them, find interesting stories in them, and share them with our stakeholders. But we also understand the dangers in releasing certain numbers; especially small numbers that could allow end users to identify specific people. That’s where suppression comes in.
In theory, suppression sounds simple: If a number is less than X, don’t show it on your dashboard. And 90% of the time it is that easy. But then there are times when suppression in Tableau becomes a twisted mess of nested table calculations. So let’s look at some suppression examples and how to handle them. You can follow along by downloading the Excel files and Tableau workbook from my Google Drive.
For these examples, you first need to set up two parameters: Suppression String and Suppression Max. We’re going to use these instead of hardcoding suppression values to prepare for if/when your end user decides to change their mind. You know, even though they promised they wanted a “^” to indicate anything less than 10, now they think an “*” looks better and actually, anything under 20 needs suppressed, they can and will change their minds at the last minute. In that case, you can just update the parameter and not a million calculations. Here is how to set up those two parameters:
Once you have the parameters created, there are a couple ways to incorporate them to suppress data.
The first way is the easiest: you have a single graph or column in a table, and you don’t need to show the grand totals:
In the above bar graph, the requirements specify that anything less than 10 needs to be suppressed. Because you’re hiding the grand totals, you can just suppress anything less than 10 and you’re good to go. The formula for these is:
if [VALUE]<[Suppression Max] then [Suppression String]
else str([VALUE])
end
Note: because the [Suppression String] is a string, you have to convert your numbers to strings. There’s a bit more you’ll need to do if you need the numbers rounded, converted to percentages, etc. but this is the basic logic. If you need a numeric suppression, such as for axes, then you can write:
if [VALUE]<[Suppression Max] then NULL
else [VALUE]
end
There! Easy suppression! End of blog, right?
Nope. Now your end user needs the grand totals showing. In the bar graph example above, since only one number is suppressed, if you add a grand total in then your end users can subtract all the non-suppressed values from the grand total to figure out the suppressed value. That’s where complementary suppression comes in. Complementary suppression is when you need to suppress any number under a given value, and then the next lowest value after that, so that you’ll always have a minimum of two values suppressed and people cannot math out what the suppressed values are.
Now, at this point, it would be easiest to do the suppression on the database side and just bring the numbers with the suppression into Tableau. If you have that option, TAKE IT. But sometimes you don’t. So let’s create some calcs that will make this crosstab:
Look like this:
In the above table, the requirements are “implement complementary suppression for any value less than 10, and if the two suppressed values add to less than 10, supress the next lowest value.” Taking a look at the first table, you can see that there are two values less than 10, and that is the 2 at row A and 7 at row L. However, since 7+2 is 9, which is less than 10, we have to suppress the next lowest value as well, which is 17 at row J.
Why go through all of this? Some places have very strict suppression requirements so this level of suppression is required.
Let’s play whack-a-number!
Step 0: Initial Setup of Data
In order to make sure we’re suppressing the lowest values, we’ll need a couple of variables setup before we start the whack-a-mole. We will set a few extra variables in this step because after this example we’re going to do one more, a full crosstab, where we’ll need some extra set-up.
Variable Count
This is your measure. In this example it’s just a simple sum(1).
Value Formatted
//FORMATTING THE STRING CONVERTION SUM OF VARIABLE COUNT TO SHOW IN NUMBER FORMAT BY ADDING ‘,’
//If there are additional data types their string conversion would happen hereREGEXP_REPLACE(STR(sum([Variable Count])),”(\d)(?=(\d{3})+$)”,”$0,”)
Because the final table will have to be strings, not numbers, to accommodate the suppression string, we need to make sure the final values are formatted as strings as well. I use this REGEXP_REPLACE formula when I’m changing number values to strings because it will add in thousands separator values, making the table formatted in a much more user-friendly way.
Rank by row
rank_dense(sum([Variable Count]),’asc’)
This ranks your values and will be calculated along table across. This way we have an order for which values in the row are highest/lowest.
Rank by column
rank_dense(sum([Variable Count]), ‘asc’)
This ranks your values and will be calculated along table down at the end when we start editing our nested table calcs. This way we have an order for which values in the column are highest/lowest.
Size row
size()
The size function is awesome and counts the number of rows/columns in a view, depending on if it’s set up as table across or table down. When you turn on Grand Totals, the Grand Total column/row will have a size() of 1. For this, we need two size calculations so that one can be table across and one can be table down, so we can ensure our totals aren’t being suppressed.
Size column
size()
Suppression Example 2: Suppress a one row or column table with a grand total
Step 1: Initial Suppression
First see if any suppression even needs to happen
1. Suppress Value Table Initial
//CHECK IF VARIABLE COUNT AT ITEM LEVEL IS LESS THAN SUPPRESSION VALUE
{include [Group by Item Row]:sum([Variable Count])}< [Suppression Max]
Step 2. Secondary Suppression
Now we need to make sure at least two values are suppressed, and that those two values, when added together, are greater than the suppression cutoff (in this case, they need to add to be more than 10).
2. Suppress Value Table Secondary
if [Size column]=1 then FALSE
//Nothing in this column is being suppressed
elseif not total(sum(if [1. Suppress Value Table Initial] then 1 else 0 end))>0 then FALSE
// Is the sum of the suppressed values greater then the suppression max
elseif total(sum(if [1. Suppress Value Table Initial] then [Variable Count] else 0 end))
>[Suppression Max]
then
// We don’t need secondary suppression
if max([1. Suppress Value Table Initial]) then TRUE
else FALSE
END
// Suppress the inital suppression
elseif max([1. Suppress Value Table Initial]) then TRUE
//Find the lowest value that is not suppressed and use it for complementary suppression
//Higher ranked values will be false and therefore not suppressed
elseif [Rank by column]
-total(sum(if [1. Suppress Value Table Initial]
then 1 else 0 end))=1 then TRUE
else FALSE
end
Step 3: Put it all together
Now that we’ve flagged the initial and secondary suppression, put them all together. Because this table has a grand total, we want to ensure that we don’t over or under suppress that value, so we use the Size variable to account for that.
3. Suppression Final Table
if [Size column]=1
//it’s a total row but still under the suppression max
then if sum([Variable Count])<[Suppression Max] then [Suppression String]
else [Value Formatted]
end
elseif [2. Suppress Value Table Secondary] then [Suppression String]
else [Value Formatted]
end
You will need to ensure the table calc is calculating correctly. To do that, bring 3. Suppression Final Table to the Text marks card, right-click, and go to “Edit Table Calculation.” Make sure they are all set up like the following:
And voila! You have suppression!
Adding in the percentages takes a bit more work. I added those calculations in the Tableau workbook, you can always go check those out.
Example 3: Suppressing a Crosstab
All right, that wasn’t too bad, was it? For my final suppression example, I want to suppress a crosstab. This will make the table seem like a cake walk. Here’s the crosstab:
We want it to look like this:
In the above tables, the requirements are “implement complementary suppression for any value less than 10.” Taking a look at the first table, you can see that there is only one value less than 10, and that is the 2 at row A, Group 1. So why are 6 values now suppressed? Let’s walk through it:
- The 2 is suppressed because it’s less than 10
- The orange 15 and orange 17 are suppressed because of the complementary suppression. If those weren’t suppressed then you could add together all Groups 2, 3, and 4, subtract them from the grand total, and figure out that the suppressed value is 2.
- When the secondary suppression is applied, the 15 is the only value in Group 2 suppressed, and the 17 is the only value in row J suppressed. So now someone could add together all of row J, subtract from the grand total, figure out that is 17, and then do that again to Group1 and figure out that the value is 2. So then the tertiary suppression comes in. The red 20 is suppressed to be complementary to Group2 (orange 15) and the the red 11 is suppressed to be complementary to row J (orange 17).
- Doing that caused the red 11 to be the only value in Group 3 suppressed, so now the green 16 in row L is suppressed. This is Quad suppression. In this example we stop here because the hope is that by going through the quaternary level, even if there is a single value (like the green 16), there’s enough other values suppressed that people can’t math back to the yellow 2.
Step 1: Identify the initial values that need to be suppressed.
Create two calculations to do the initial suppression. Because we’re going both down and across with the complementary suppression we have to create calculations that go in both directions.
This looks at the dimension that is on the Rows and flags anything that is less than the parameter you set up to be the max suppression value.
1a. Suppress Value Row Initial
Do the same thing for Columns.
1b. Suppress Value Column Initial]
The outcome of these formulas are true/false, is it above/below the suppression value.
Step 2: Start looking at the secondary suppression
You’ll create two more calculations to look at the secondary suppression. This is just looking to see if there is any primary suppression in rows or columns, and then doing that first look to suppress a second value so that specific row or column cannot be added together (the orange values from this table).
Your calculations are:
2a. Suppress Value Row Secondary
//First, check if nothing in this column is being suppressed and if nothing is being suppressed then default to false
if(if not total(sum(if [1a. Suppress Value Row Initial] then 1 else 0 end))>0 then FALSE// Is the sum of the suppressed values greater then the suppression max
elseif total(sum(if [1a. Suppress Value Row Initial] then [Variable Count] else 0 end))
>[Suppression Max]
then// We don’t need secondary suppression
if max([1a. Suppress Value Row Initial]) then TRUE
else FALSE
END// Suppress the initial suppression
elseif max([1a. Suppress Value Row Initial]) then TRUE//Find the lowest value that is not suppressed and use it for complementary suppression
//Higher ranked values will be false and therefore not suppressed
else [Rank by row]
-total(sum(if [1a. Suppress Value Row Initial]
then 1 else 0 end))=1
end) then [Suppression String]
else ([Value Formatted])
END
2b. Suppress Value Column Secondary
//Nothing in this column is being suppressed
if(if not total(sum(if [1b. Suppress Value Column Initial] then 1 else 0 end))>0 then FALSE
// Is the sum of the suppressed values greater then the suppression max
elseif total(sum(if [1b. Suppress Value Column Initial] then [Variable Count] else 0 end))
>[Suppression Max]
then
// We don’t need secondary suppression
if max([1b. Suppress Value Column Initial]) then TRUE
else FALSE
END
// Suppress the initial suppression
elseif max([1b. Suppress Value Column Initial]) then TRUE
//Find the lowest value that is not suppressed and use it for complementary suppression
//Higher ranked values will be false and therefore not suppressed
else [Rank by column]
-total(sum(if [1b. Suppress Value Column Initial]
then 1 else 0 end))=1
end) then [Suppression String]
else ([Value Formatted])
END
Step 3. Let the whacking begin!
With those calculations, we have the yellow and orange suppressed. But now people could use row J to calculate what Group 1, row J value is. So we need more suppression. The 11 will be suppressed because row J has one suppressed value (17) and 20 will be suppressed because row Group 2 has only one value suppressed.
3a. Secondary Suppression on Columns
//Add secondary suppression on columns based on the rows
if window_sum(if [2a. Suppress Value Row Secondary]=[Suppression String] then 1 else 0 end)=1 and [Rank by column]<=2
then [Suppression String]
else [Value Formatted]
END
3b. Secondary Suppression on Row
//Add secondary suppression on columns based on the rows
if window_sum(if [2b. Suppress Value Column Secondary]=[Suppression String] then 1 else 0 end)=1
and [Rank by row]<=2
then [Suppression String]
else [Value Formatted]
END
Step 4: Combine all the suppression to this point
Now we have a bunch of suppression calculations, so let’s implement them in the table.
4. Tertiary suppression
//Now that secondary suppression has been done on rows, then on columns, we combine those so they are suppressed
if [2a. Suppress Value Row Secondary]=[Suppression String] then [Suppression String]
elseif [2b. Suppress Value Column Secondary]=[Suppression String] then [Suppression String]
elseif [3a. Secondary Suppression on Columns]=[Suppression String] then [Suppression String]
ELSEIF [3b. Secondary Suppression on Row]=[Suppression String] then [Suppression String]
else [Value Formatted]
END
Step 5: Quad Suppression
This last suppression will take care of the green 16 in the spreadsheet above. Technically, that will only have one value suppressed but honestly, we could play whack-a-mole until every value is hidden. So in this example we stopped at quad, because the main goal of all of this is to hide the initial yellow value of 2. With 4 levels of suppression, users should not be able to figure out that value, even if they can figure out this 16.
5a. Quad suppression in row
//Finally, since after adding the column secondary suppression could result in a row having just 1 value suppressed again,
//Go back through the rows, check for just one suppression, and re-suppress.
if WINDOW_SUM(if [4. Tertiary suppression]=[Suppression String] then 1 else 0 end)=1 and [Rank by row]<=2 then [Suppression String]
else ifnull([4. Tertiary suppression],’0')
END
5b. Quad suppression in column
//Finally, since after adding the column secondary suppression could result in a row having just 1 value suppressed again,
//Go back through the rows, check for just one suppression, and re-suppress.
if WINDOW_SUM(if [5a. Quad suppression in row]=[Suppression String] then 1 else 0 end)=1
and [Rank by column]<=2 then [Suppression String]
else ifnull([5a. Quad suppression in row],’0')
END
Step 6: Remove some over-suppressed values
With the calculations above, it is possible to have your values over-suppressed. So let’s do some final calcs to help make sure totals and such aren’t actually being suppressed. First, let’s calculate some totals, mins, and maxes along rows and columns.
6a. total cell values suppressed in row
window_sum(
if [5b. Quad suppression in column]=[Suppression String]
then sum({include [Group by Item Row]:sum([Variable Count])}) end)
6b. min cell values suppressed in row
window_min(
if [5b. Quad suppression in column]=[Suppression String]
then min({include [Group by Item Row]:sum([Variable Count])}) end)
6c. max cell values suppressed in row
window_max(
if [5b. Quad suppression in column]=[Suppression String]
then max({include [Group by Item Row]:sum([Variable Count])}) end)
6d. total values suppressed in column
window_sum(
if [5b. Quad suppression in column]=[Suppression String]
then sum([Variable Count]) end)
6e. min suppression of columns in row
window_min([6d. total values suppressed in column])
6f. min values suppressed in column
window_min(
if [5b. Quad suppression in column]=[Suppression String]
then min([Variable Count]) end)
Step 7: Put it all together! Part 1.
Because the total can get suppressed, which can cascade into over suppression, the final variable needs to have checks for that. So here’s the first step of putting it all together.
7a. suppression with total checks row
if [Size row]=1
and [6a. total cell values suppressed in row]>[6b. min cell values suppressed in row]
and sum([Variable Count])>[Suppression Max]
then ‘Show row total’ //[Value Formatted]
elseif [6c. max cell values suppressed in row]=sum([Variable Count]) then ‘hide row’
else [5b. Quad suppression in column]
end
7b. suppression with total checks column
if [Size column]=1
and [6e. min suppression of columns in row]<[Suppression Max]
and [Rank by row]<=2
then [Suppression String]//’hide row total’
elseif [Size column]=1
and [6d. total values suppressed in column]>[6f. min values suppressed in column]
and sum([Variable Count])>[Suppression Max]
then [Value Formatted]
elseif [Size column]=1 then [5b. Quad suppression in column]
elseif [7a. suppression with total checks row]=’Show row total’ then [Value Formatted]
else [5b. Quad suppression in column]
end
8a. min suppressed in column
window_min(if [7b. suppression with total checks column]=[Suppression String]
then sum([Variable Count]) END)
9. final suppression (yes, that does say final!!)
if [Size row]=1
and [8a. min suppressed in column]<[Suppression Max]
and [Rank by column]<=2
then [Suppression String]//’hide row total’
else [7b. suppression with total checks column]
end
Step 7: Put it all together! Part 2: setting up the table.
You did it! You have all your calculations pulled together! Now let’s assemble the actual crosstab.
Put Group by Item Column on columns and Group by Item Row on rows. Add 9. final suppression onto the Text marks card. This is a table calculation, so we need to set up all the nested calculations underneath. I have screenshots of each one below.
Then you’re done! Congrats on finally ending your game of whack-a-mole. Now that you’ve seen the steps, do yourself a favor and try to get this all implemented at the datasource level so you don’t have to set this all up.
Nested Calculation Order
