5 Replies Latest reply on Jun 8, 2017 8:34 AM by Lucky Josh

    How to perform calculations on a  field which holds both string and numeric data

    Lucky Josh

      Hi,

      There is a field which holds numeric as well string values. My requirement is to use both - string or numeric from that field depending on the situation.

      In below example "Result" column holds values for numbers and string. if its number then I need to take average for date time and if its String then simply display the value.

      I am trying to do so but if I change "Result" to measure then string values will be null and if keep it as a dimensions then I can not do aggregation for numeric values.

      Any help on this please?

        

        

      AttributeResultDate
      AAA124/20/2017
      ZZZ134/20/2017
      SSSPass4/21/2017
      QQQFail4/22/2017
      WWW44/23/2017
      EEE94/24/2017

       

      Message was edited by: Lucky Josh

        • 1. Re: How to perform calculations on a  field which holds both string and numeric data
          Rody Zakovich

          Hey Josh,

           

          You could try this.

           

          IF ABS(INT([Result])) >= 0 THEN  STR(ROUND({FIXED [DATE] : AVG( INT([Result])) },2))

          ELSEIF NOT ABS(INT([Result])) >= 0 THEN [Result]

          END

           

          Basically this evaluates if the [Result] string is numeric, if it is, then we find the Avg of it based on the Date field, round, and convert to a string. If it is a String, then we just display the Results.

           

          If you are placing [Date] on the Columns/Rows Shelf, you should be able to drop this in.

           

          If this isn't what you were looking for, please let me know.

           

          Regards,

          Rody

          1 of 1 people found this helpful
          • 2. Re: How to perform calculations on a  field which holds both string and numeric data
            Prayson Wilfred Daniel

            IIF(ISNULL(INT([Result]))=False,STR(ROUND({FIXED [Date]:AVG([Result])},2)),[Result])

            1 of 1 people found this helpful
            • 3. Re: How to perform calculations on a  field which holds both string and numeric data
              Lucky Josh

              Hi

              I appreciate your response.

              Below is the scenario I am trying to solve.-

              I have below sets of data where ‘Result’ column holds both integer as well as string data.

               

              Attribute Name

              Max value

                               Mean Value

                            Sample count

              Result

                                 Date

              AAA

              15

              10

              8

              12

              4/20/2017

              ZZZ

              15

              10

              9

              13

              4/20/2017

              SSS

              10

              Pass

              4/21/2017

              QQQ

              8

              Fail

              4/22/2017

              WWW

              8

              5

              6

              4

              4/23/2017

              EEE

              7

              5

              7

              9

              4/24/2017

               

              I am trying to sketch the below graphs where for each attributes, I have associated measures like Max value, Mean value and Sample count. I also want to add ‘Result’ into the graph but I cannot because it hold string and numeric value.

              Can you please advise any workaround for this? I have attached sample workbook herewith.

              Capture.PNG

               

               

              Regards

              • 4. Re: How to perform calculations on a  field which holds both string and numeric data
                Joshua Milligan

                Hi Lucky Josh,

                 

                If you want a separate Measure that tracks the text values of "Pass" / "Fail", but you want to use it in Measure Names / Measure Values, then you'll have to make sure the value is numeric, but you can use formatting to get back to text (since you only have 2 values -- you could use this technique up to 3)

                 

                First, your calculation would look something like:

                 

                Then, you can adjust the default formatting for the Result field - use the drop down on the field under Measures and select Default Properties > Number format....

                 

                Apply a custom format:

                 

                 

                Numeric formats usually consist of a format string like #,###.#0  to indicate where digits, commas, decimals should be placed.  But, you can also hard-code string values.  The semi-colon separates positive formatting from negative (an additional semi-colon would allow you to have separate formatting for 0)

                 

                Then, when you use the field in your view under Measure Values, you'll get something like:

                 

                 

                Hope that helps!

                Joshua

                1 of 1 people found this helpful
                • 5. Re: How to perform calculations on a  field which holds both string and numeric data
                  Lucky Josh

                  perfect. thank you for the nice explanation.

                  1 of 1 people found this helpful