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

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.

 Attribute Result Date AAA 12 4/20/2017 ZZZ 13 4/20/2017 SSS Pass 4/21/2017 QQQ Fail 4/22/2017 WWW 4 4/23/2017 EEE 9 4/24/2017

Message was edited by: Lucky Josh

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

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

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

Hi

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.

Regards

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

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

perfect. thank you for the nice explanation.

1 of 1 people found this helpful