7 Replies Latest reply on Feb 28, 2018 1:41 PM by Eric Hammond

    Ratio display

    deepesh g

      Hi All,

      I have 2 measures in the report and need to display the ratio of those measures  in a specific format as given below .How can I  implement this .Please help

        • 1. Re: Ratio display
          Zhouyi Zhang

          Hi, Deepesh

           

          Not sure what you are going to achieve from your sample workbook. can you explain more details?

           

          ZZ

          • 2. Re: Ratio display
            Michael Hesser

            Hi Deepesh;

            Are you trying to determine if your numbers match the given ratio?

            If so, I might begin by reshaping your data: Split the Ratio into two fields: [Ratio - Split 1] and [Ratio - Split 2]. These are the default names Tableau will generate once [Ratio] is split on your GUI.

             

            Next, a quick calculation:

             

            Test Ratio

            IF [A] * [Ratio - Split 2] = [B] * [Ratio - Split 1] THEN "Correct Ratio"

            ELSE "IMPROPER RATIO"

            END

             

            If you are trying to GENERATE B from A, you can use a similar bit of logic:

             

            B Ratio

            [A]*[Ratio - Split 2]/[Ratio - Split 1]

             

            Hope this helps-- Michael

            • 3. Re: Ratio display
              Jyothisree Rayagiri

              I think he has the fields A and B and wants to build the ratio field in the 1:2 format in a tableau calc.

              • 4. Re: Ratio display
                deepesh g

                I havethe fields A and B and wants to build the ratio field in the 1:2 format in a tableau calc.

                • 5. Re: Ratio display
                  Michael Hesser

                  Interesting!

                   

                  If you're trying to create the ratio, I think you'll need the GCD (Greatest Common Divisor) [or is it Denominator?].

                   

                  Joe Oppelt had some words to that effect in this post:

                  How can I calculate the Greatest Common Divisor for two numbers?

                   

                  Good luck! --Michael

                  • 6. Re: Ratio display
                    Michel Pitz

                    Is it possible to implement  what Michael Hesser posted about Joel's idea with Python integration. It is not that difficult

                    • 7. Re: Ratio display
                      Eric Hammond

                      If possible, help your audience to understand the benefits of viewing the relationship between A and B in decimal form instead of as a ratio.  If they insist on seeing a ratio then the following calculation may help:

                       

                      STR(ROUND([A]/[A]

                      * IF ROUND(            ([B]/[A]) % 1                ,4) = 0 THEN 1 ELSE           (1 / (([B]/[A]) % 1))

                      * IF ROUND(      (1 / (([B]/[A]) % 1)) % 1          ,4) = 0 THEN 1 ELSE      1 / ((1 / (([B]/[A]) % 1)) % 1)

                      * IF ROUND((1 / ((1 / (([B]/[A]) % 1)) % 1)) % 1    ,4) = 0 THEN 1 ELSE 1 / (1 / ((1 / (([B]/[A]) % 1)) % 1) % 1)

                      END

                      END

                      END

                      ,0))

                       

                      + ":" +

                      STR(ROUND([B]/[A]

                      * IF ROUND(            ([B]/[A]) % 1                ,4) = 0 THEN 1 ELSE           (1 / (([B]/[A]) % 1))

                      * IF ROUND(      (1 / (([B]/[A]) % 1)) % 1          ,4) = 0 THEN 1 ELSE      1 / ((1 / (([B]/[A]) % 1)) % 1)

                      * IF ROUND((1 / ((1 / (([B]/[A]) % 1)) % 1)) % 1    ,4) = 0 THEN 1 ELSE 1 / (1 / ((1 / (([B]/[A]) % 1)) % 1) % 1)

                      END

                      END

                      END

                      ,0))

                       

                       

                      For ratios that are more difficult to resolve this returns a ratio that isn't exact.  For example, if A is 57 and B is 672, it returns 6:75 instead of 19:224.  For more granularity, add more IF statements following the pattern in the calculation; this will result in a correct ratio of 19:224 in the example given.

                      1 of 1 people found this helpful