3 Replies Latest reply on Jun 6, 2017 10:52 AM by Matthew Lauer

    Field Data Type Formatting

    Matthew Lauer

      I am having an issue where I have a calculated field that shows one of two fields based on the value of a parameter selected. The two possible fields are dollars and man hours. I need it so that when dollars is selected he data type is formatted as dollars $###.## and man months is formatted like ##.#. I have tried setting the data types of the individual fields before they are called by the calculated field but it did not retain the formatting. Please let me know if you have any ideas.

        • 1. Re: Field Data Type Formatting
          Michel Caissie

          Matthew,

           

          You can duplicate your calc,  one for each parameter value,  each one returning null for the other parameter value.

           

          calcDollars

          case param

          when 'Dollars' then [dollars]

          else null

          end

           

          calcManMonth

          case param

          when 'ManMonth' then [manMonth]

          else null

          end

           

          Set the default formatting accordingly, and put both on the Text shelf.  Each mark will get two labels, but since one will always be null only the one with the right formatting will display.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Field Data Type Formatting
            Wilson Po

            Hey Matt,

            That sounds like an interesting way that the data is organized - can you attach a workbook for us to take a closer look?  We might be able to recommend some changes to the data structure that will make it easier to work with this field (especially since you don't wan to aggregate man hours and dollars together as a SUM/AVG/etc.). 

             

            Calculated fields might help out as well if you have an identifier for each row about what type of data that measure represents - something like:

            IF [Type]="Dollars" THEN [Measure]  END // 1 calculated field that represents the rows that are just Dollars

            IF [Type]="Man Hours" THEN [Measure]  END // 1 calculated field that represents the rows that are just man hours

            this separates the measures to being two separate measures, which may be easier to set a fixed formatting to. 

            1 of 1 people found this helpful
            • 3. Re: Field Data Type Formatting
              Matthew Lauer

              Thank you guys. Splitting it in to two different calculated fields was the way to go. Thanks again!