4 Replies Latest reply on Oct 4, 2018 9:05 AM by Leigh Fonseca

# Calculate Aspect Ratio and display it at a string (16:9, 4:3)

I have found several posts for calculating a ratio and displaying it as a string, but all seem to display the ratio over 1 (e.g., 2:1, 4:1).  While I could group these values, there are a large number of possible inputs and it's ever changing.

The dimensions are: Vertical Resolution and Horizontal Resolution.  The Aspect Ratio is determined by Vertical Resolution / Horizontal Resolution.  In Tableau, this will output a decimal (Aspect Ratio Calculated).  In Excel, you can calculate the ratio and set the number format to ##/## and it will display 16/9.  I can't seem to find any way to get Tableau to do this.

In this case, it doesn't matter whether the separator is a colon (:) or a forward slash ( / ).  What is required is that the smallest whole number is the provided (e.g., the 9 in 16/9).

 Horizontal Resolution Vertical Resolution Aspect Ratio Aspect Ratio Calculated 146 72 36/73 0.493150685 1280 720 9/16 0.5625 1080 1920 16/9 1.777777778 480 854 137/77 1.779166667 1312 2560 80/41 1.951219512 1440 3120 13/6 2.166666667

In the attached workbook, the field Aspect Ratio was calculated and formatted in Excel.  The field Aspect Ratio Calc performs the same calculation.  It will not correctly display the values when using the ##/## Excel formatting.

Any suggestions are graciously welcomed.

Thanks!
Leigh

• ###### 1. Re: Calculate Aspect Ratio and display it at a string (16:9, 4:3)

To achieve the desired result, you need to be able to derive the highest common divisor for the pair of resolutions. This requires iteration and Tableau does not provide a looping option similar to what you have in programming languages. So, you can look at iteration using R or Python to get the highest common divisor and then use that value in Tableau to get you the desired result.

• ###### 2. Re: Calculate Aspect Ratio and display it at a string (16:9, 4:3)

Thanks, Hari, for your suggestion.  I need to deliver a solution that is done solely in Tableau or Tableau Prep.  I could group the items or create a calculated field, but I'd prefer a mathematical solution.

• ###### 3. Re: Calculate Aspect Ratio and display it at a string (16:9, 4:3)

I would suggest using Python then since Tableau provides the TabPy server. You can then use the Tableau's SCRIPT functions to pass the required values to the Python server and your value can be computed and returned back to Tableau.

• ###### 4. Re: Calculate Aspect Ratio and display it at a string (16:9, 4:3)

Hi Hari,  Unfortunately, TabPy is not an option for Tableau Online deployments.  Thanks anyway.