Making Friends With Modulo %
Michael Hesser Mar 15, 2019 5:03 AMHave you ever heard a word for the first time, only to hear that word used three times in the same day? Well the same thing has happened to me: though it’s not with heterochromia, but with Tableau’s modulo function. Seems like I’ve been hearing it everywhere!
If you’re not familiar with modulo, you may find it allows you to do some pretty fancy stuff to spruce up your visualizations. So let’s start with the basics and let me tell you what I know about modulo!
What It Is: The modulo operation finds the remainder after the division of one number by another. If you’re one of those highfalutin’ mathematics types, you know this resultant value more officially as the modulus. Remainder, modulus—call it what you want (just don’t call me Shirley).
How to Get the Modulus from Tableau: The percent key (“%”) also serves as the modulo operator, but we’ll also need a couple of other things:
 We need the dividend, or the number we want to divide
 We also need the divisor, or the number we want to divide by
When we put them all together, it looks like this:
(Seven divided by three is two, with a remainder of one)
And as long as we’re talking gradeschool math: you may recall that dividing the dividend by the divisor returns the quotient, and the whole number left over is the remainder, or modulus.
More Modulo Examples:
Notice the pattern as the dividend increases? Therein lies modulo's clever property: the modulus incrementally increases before returning to 0!
So How Can Modulo Help Me Do Cool Stuff?
Modulo Can Return Time Parts
One potential use for modulo is returning date parts. For example, if you have a large value in minutes, and want to just see the minutes (sans hours) portion of it, you might see something like this:
DATEDIFF(‘minute’, [Login Time], [Logout Time]) % 60
This computes the minute part of the time difference and will always be a number between 059. (Though there may be more efficient ways to do it)
Modulo Can Turn Seconds into HH:MM:SS
If you have a value in seconds, it may be necessary to convert it into a more usable format. Modulo can help!
Hours Calc
INT([Total Seconds]/3600)
Minutes Calc
INT(([Total Seconds])%3600)/60)
Seconds Calc
([Seconds Total]%3600)%60
And blend them together:
Time Calc
MAKETIME([Hours Calc],[Minutes Calc],[Seconds Calc])
You may need to change the date format of Time Calc to custom
...and show as ATTR(Time Calc)
Use Modulo to Create n Number of Columns
Modulo can let you more easily define matrices. Matrices can help you preserve valuable realestate by turning a long list into a more compact one. For example, suppose you have a field of states. But instead of one long boring column, you’d rather display them in 2 columns, or 3 columns, or any other number? Modulo can help!
Long, inefficient single column:
Slightly more useful twocolumn representation:
Threecolumn approach, and perhaps a better use of space!
Here’s how I did it:
Since the column count will be userdefined, let’s start with a parameter:
This will allow our user to select number of columns from 1 to 10.
Next, let’s count how many unique elements we have (in this instance, States):
Total States
{fixed [Country]:countd([State])}
//This gives us a count of all the states
Now that we know how many elements we have as well as how many columns we need we can deduce how many rows will be required:
States Per Column
ceiling(avg([Total States])/[Choose # Column])
//ceiling rounds the number of rows UP
//the “avg” allows us to mix aggregated and nonaggregated values
Let’s make sure each state has a unique number. We can do this by ranking them by name:
State Index
Rank(attr([State]),'asc')1
//This simply puts all the states in alpha order and assigns them a number
//Note I’m subtracting one from the calc. This is because we want our first value to be 0, not 1
Now we’re ready to fill in our matrix!
State Column
INT(([State Index])/[States Per Column])
//This calculates the correct column for each state
State Row
[State Index]%[Choose # Column]
//You’ve waited for it—the modulo operator!
//This little guy calculates the correct row for each state
Now:
 Display [Choose # Column] Parameter
 Drop [State] onto Text
 Make {State Column] and [State Row] discrete
 Drop [State Column] on Columns; compute using [State]
 Drop [State Row] onto Rows; compute using [State]
And with a little luck, you should be in business!
Mnemonic Trick: I always forget if I should be using modulo when I generate my row value or column values, so this rhyme “modulo goes on the rows” helps me out.
More Matrix Fun…
Using the same concepts, you can make other types of matrices, such as this curious “chess board” of state data:
This remains SORTABLE, so you can quickly resort it into something like this:
Pretty, huh? I'll let you decide if it could be useful (after all, that should be key, right?)
Modulo Can "Jitter" Your Numbers
Oftentimes we're faced with a large number of elements. Displaying them all at once can be problematic.
Using the same principle, we can use mod to introduce a new column that simulates a random number, thus spreading values out. What may initially appear as hundreds of overlapping values is now spread out and (somewhat) easier for the user to play with.
Before, with one painful column
After, using mod and index to generate an xcolumn value:
While there are MANY ways to "jitter" your data mod will let you easily "paramatarize" a value that shows your viz in the best light. I'm not saying it's the finest use of mod just an option.
Check out the attached workbook to see the example in action.
Modulo Meets Magneto: Displaying Trellis/Panel Charts
Michele Tessari gave an aweinspiring presentation during TC17; he deserves all the credit for this next part as it’s far beyond my skills!
You can watch Michele work his magic in the TC17 Telecast called Balancing Art and Analysis, found here: http://tclive.tableau.com/Library/Video?vCode=17BI012
In a nutshell: how can we easily display a complex relationship over time (e.g. correlation between Mobile Phone Usage and Internet Usage) for a large number of elements (~200 countries), without them creating an unintelligible “tossed spaghetti graph”?
Answer:
 Start by using our friend modulo to create a matrix large enough to display all the elements...
 Calculate the slope of the relationship (using CORR, COVAR, and VAR)
 And then sort the elements by slope within the matrix.
The result is a graph that looks like a magnetic field or storm front: useful in that is allows the user to quickly visually interpret a large number of elements for outliers, with the added benefit of being aesthetically pleasing.
My attempt to duplicate Michele's awesome work looks something like this:
I've attached a workbook so you can see it in action (and reverse engineer, as well).
Questions:
 How & when do YOU use modulo?
 Are there ways to use modulo more “properly”?
 What more do folks need to know about modulo?
Big thanks to: Michele Tessari , Michael Someck and Ben Neville for their inspiration!

World Indicators Slope.twbx 220.6 KB

Modulo.twbx 535.0 KB