How to get number from string

Questions & Answers
, ,
1

Hello community,
I am trying to make a calculation between two custom fields, goal and current result, where I want to show the fulfillment in percentage.
The users who fill out these fields in jira do so without following a single format, so I need to clean the data before calculating compliance.

I’m trying to get the number from a string, for example:
From the string “S/ 12,394.23 soles” I expect the result 12394.23, because after that I want to do calculations with that number, to divide with another.
I have tried this formula:
Val(Replace(ExtractString([Measures].[Issue Meta],"(\d+\.?,?\d*)",1),",",""))

However, I get the error
Failed to execute query. Error message:
java.lang.ClassCastException@6579bb22

I hope you can help me.

2

Hi @feluis23,
​
Your expression generally looks great.
​The problem is that sometimes the ExtractString() might return an empty response that cannot further be processed by Replace(). That raises the script error.

​You might swap the order of these functions for better performance. First, remove the excess characters and then process the string.

Val(
 ExtractString(
  Replace(
   [Measures].[Issue Meta],
   ",",""),
  "(\d+\.\d*)",1)
)

​
​The Replace() will get a string to process, and the ExtractString() will retrieve the numerical part.
​
Regards,
​Oskars / support@eazyBI.com