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

    Making Friends With Modulo %

    Michael Hesser

      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


      //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



      • 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 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).



      • 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 2 Previous Next