3 Replies Latest reply on Aug 12, 2016 10:32 PM by Kurt Heisler

    Conditionally format values with varying decimal levels

    Kurt Heisler

      I have data showing data on various indicators, often by subgroups (like performance by ***, race, etc.). All of the values are stored in a single field called Value. Some of the original values have no decimals, some have 1, and some have 2.

       

      The underlying data look like:

       

      I'd like to create a table like below that shows the values with their actual value (i.e., down to the decimal level and not rounded). Obviously, I can apply only one format to Values at a time.

       

       

      How can I conditionally format these based on the level of decimals stored in the underlying data?

       

      In the underlying data, I did add variables for DataFormat (Number, Percent) and Decimal (0,1, or 2). I'm okay not showing "%s" for the Percents if that's too tricky, so maybe the Decimal field is all that's needed.

       

      The solution I've come up with in a similar situation is to use a calculated field that returns a string, [Value2 Format % or N], in place of Value.

       

      Value2 Format % or N:

       

      IF attr([DataFormat]) <> "Number"

      THEN str(int(mid(str(round(min([Value2]),3)),3,2))) + "." + str(int(mid(str(round(min([Value2]),3)),5,1))) + "%" else str(round(min([Value2]),0))

      END

       

      But in this case I think need something like:

       

      IF attr([Decimals]) = 0

      THEN // show original values, and format number with commas in thousands place (e.g., "1,000")

      IF attr([Decimals]) = 1

      THEN // show original values, and format with one decimal (e.g., "92.6")

      IF attr([Decimals]) = 2

      THEN // show original values, and format with two decimals (e.g., "60.75")

      END

       

      Any suggestions? Thank you.