1 2 Previous Next 25 Replies Latest reply on Feb 13, 2020 11:30 AM by Michael Hesser

# Making Friends With Modulo %

Have 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 high-falutin’ 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 grade-school 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 0-59. (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 real-estate 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 two-column representation:

Three-column approach, and perhaps a better use of space!

Here’s how I did it:

Since the column count will be user-defined, 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 non-aggregated 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]%[States Per 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?)

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 x-column 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 awe-inspiring 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: Beautiful data: Balancing art and analysis - YouTube

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”?

• 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!

• ###### 1. Re: Making Friends With Modulo %

Terrific directions!
I used modulo to grid a series of charts. (I'm experimenting w pies and don't know that I'll keep them) In this use case you can see differences.  FYI each pie is total county ER visits with the (orange) angle representing the proportion that are not true emergencies and could be treated at urgent care or primary care physician offices. The first county has more than half of its ER visits non-emergencies suggesting a lack of alternatives.  The last count has only a quarter of its ER visit non-emergent suggesting easier access to alternate care settings.

• ###### 2. Re: Making Friends With Modulo %

Very good article. Thanks for the explanation.

• ###### 3. Re: Making Friends With Modulo %

Glad you liked! As Anne Seaman suggested above, modulo is a great little tool when you're trying to split vizzes into multiple "cells."

• ###### 4. Re: Making Friends With Modulo %

I never see this easy way you explain. Very well done. Thanks Bravo.

• ###### 5. Re: Making Friends With Modulo %

Thank you michael for sharing.

I've watched Balancing art and analysis which is really amazing! I wonder if you had the workbook Michele Tessari present in this session?

my email is sheeptian@gmail.com

Thank you.

YT

• ###### 6. Re: Making Friends With Modulo %

Hello tian.yang.4 !

I tried to reproduce it above as World Indicators Slope.twbx above.

That will have all the active calculations in it, plus more.

I'm afraid the link to Michele Tessari's awesome presentation during TC16 is no longer active, but perhaps he will see these references and can send you a copy?

Like yourself, I was very entranced by the visualization!

• ###### 7. Re: Making Friends With Modulo %

thank you Michael for sharing !

I watched that presentation serveral times and it's really clear and very easy to understand!

• ###### 8. Re: Making Friends With Modulo %

Thank you for sharing Michael Hesser. I've used Modulo to generate charts like the ones you have shown - Trellis/Panel charts as they are better known. Very powerful and useful way of visualizing across dimension values in a single view but still being neat!

• ###### 9. Re: Making Friends With Modulo %

Thank you, Ajai Govind ! I've updated my post to reflect the proper naming convention

• ###### 10. Re: Making Friends With Modulo %

Thank you Michael Hesser

This helped me to work through my first trellis diagram with very little pain

By the way, I recently stumbled across this article, should anyone want to really geek out on modulo: Mod and Remainder are not the Same

Cheers,

Tim

1 of 1 people found this helpful
• ###### 11. Re: Making Friends With Modulo %

Tim Beard If you look at my pictures, you'll see I'm a big fan of "beards"! I'm glad I could help-- perhaps you could include an illustration of your trellis diagram as inspiration to others?

And point taken: modulo and remainder are not the same thing. Thank you for the article-- it was a great read and I encourage others to give it a look-see.

• ###### 12. Re: Making Friends With Modulo %

Michael Hesser I can't say too much about the content as it's customer data, but it's a trellis of jitter plots to show the distribution of tens of thousands of parts across a couple of dimensions. It looks a bit of a mess at the moment, but once one starts to use the highlighter for one of the dimensions, interesting patterns start to emerge.

I'm not even sure if the customer will use it, I was just playing around in my own time to see what else I could produce with the data

• ###### 13. Re: Making Friends With Modulo %

Thank you for sharing, Tim!

• ###### 14. Re: Making Friends With Modulo %

Very nicely laid out and extremely helpful. Thank you!!

1 2 Previous Next