4 Replies Latest reply on Jan 16, 2018 2:47 AM by Keith Peters

    Calculation decipher help required

    Keith Peters



      Apologize in advance if I've posted in the wrong area but its my first post.


      I've recently found a technique I want to use and whilst trying to work out how it was achieved I've come across a calculation that I don't understand. Is anyone able to convert this to English?


      IIF((min([Subject Number])/100)% [Columns Subject]==0, [Columns Subject], (min([Subject Number])/100)% [Columns Subject])


      Its based on the following Viz and is the calculation for 'Column Subject'. The part that's confusing me is:  (min([Subject Number])/100)% [Columns Subject]


      Link to Viz: UNICEF's State of the World’s Children Report 2016 | Tableau Public


      Thanks in advance.

        • 1. Re: Calculation decipher help required
          Norbert Maijoor

          Hi Keith


          In proper "English" something like "if % of Columns Subject is 0 then Columns Subject else % of Columns Subject"




          Hope it helps.




          • 2. Re: Calculation decipher help required
            Keith Peters

            Thanks Norbert,


            I'm on the right lines I just need to get the same answer in Excel now so I know exactly how it works it out.



            • 3. Re: Calculation decipher help required
              Paul Cawford

              Hi Keith,


              The % symbol in tableau means modulus (or remainder of a division).  The excel equivalent function is mod


              e.g. 5 % 2 = 1  (the remainder of 5 divided by 2 when dealing with whole numbers only is 1).  The equivalent excel function is =MOD(5,2)


              Alternatively 40 % 4 = 0 as 4 goes into 40 with no remainder



              So the part you are asking about:

              (min([Subject Number])/100)% [Columns Subject]


              Could be read as "First divide the minimum [Subject Number] by 100.  Next, give the remainder when this number is divided by the [Columns Subject]"


              Please note that if the min([Subject Number])/100) is not an integer excel's mod function and tableau's % operator work slightly differently i.e. tableau always disregards anything after the decimal place before calculation in the modulus operator


              e.g.  in tableau 8.9 % 3 = 2 (i.e. 8.9 is first truncated to 8.  This gives 8 % 3 = 2)

              in excel MOD(8.9, 3) = 2.9 (excel includes the fractions)


              So strictly speaking an exact excel equivalent function for tableau's in this instance would be:


              tableau = (min([Subject Number])/100)% [Columns Subject]

              excel = MOD(TRUNC((min([Subject Number])/100)), TRUNC([Columns Subject]))


              or in the simplified example:

              tableau =  8.9 % 3

              excel = MOD(TRUNC(8.9), TRUNC(3))    TRUNC truncates the number at the decimal place in excel.



              Sorry If I've over simplified things anywhere

              1 of 1 people found this helpful
              • 4. Re: Calculation decipher help required
                Keith Peters

                Thanks Paul, that's exactly what I was looking for, now I understand.