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

    Calculation decipher help required

    Keith Peters

      Hi,

       

      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.

           

          Regards,

          Norbert

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

             

            Regards

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