Eazybi help needed with converting text number with % in to number to create calc field

[Ed Richard]

Have a custom jira field called PercentDone where the entries are text based and end in a "%" sign such as 95%. Trying to create a calculated field in Eazybi reporting add on that will multiply a number by that percentage. Have spent countless hours with Val and ExtractString and all the possible arguments but just get errors returned.

Just trying to turn the 95% into a number (without multiplying it by anything does not work... so even solving that would be big) 

Val(ExtractString([Issue].CurrentHierarchyMember.get('Percent Done'),'.%(\D+)',1))

There is unfortunately no more than one example in the doc or from a google search on ExtractString. I know there are other workarounds and things I could do back in Jira so Im only dealing with a number off the bat but I want to solve the type conversion issue for future things. Also know there may be something big I'm missing and that there is an answer but I have to cap the number of hours being wasted on permutations. 

[Daina Tupule eazyBI · Accepted solution]

You are quite close using ExtractString. 

\D will represent non-digit, you would like to use \d there instead. You are using dot (.) before the percentage sign as well. It will look for some character before the dot. You would like to use a percentage sign (%) after the number as well. 

I would suggest this formula to transform 95% to 95:

Val(ExtractString([Issue].CurrentHierarchyMember.get('Percent Done'),'(\d+)%',1))

Another approach would be using a function Replace, where you can replace % in any position in the field leaving the numeric value only. Please make you have a numeric value only after this transformation. If you have some spaces consider using an additional Trim function. This will work for, 95 %, % 95, 95%

Val(Trim(Replace([Issue].CurrentHierarchyMember.get('Percent Done'),'%',"")))

Daina / support@eazybi.com

[Ed Richard]

thanks so much.. I have my number!.. quick follow-ups.. now that I can ignore the "%" sign its being treated as a whole integer so is there a way to multiply by .95 (point 95) and not the integer 95?.. also.. is there a way to concatenate in my own text character similar to the way you do in excel for example where you specify    =A2&" my custom text "& A1     in the final result?.... (hope im not pushing to hard after getting what i originally asked for)... really appreciate it Daina

[Ed Richard]

Oh wait.. for the first part, I can add a / 100.... still interested in the second concat part though

[Daina Tupule eazyBI]

Hello Ed,

Yes, division by 100 is the best way on how to get the value .95 and use it as a percentage in eazyBI. 

You can use double straight lines || to concatenate strings. You can check this example report in our demo account where we combine different information concatenating strings with numbers and dates. Numbers and dates should be transferred to strings for this. 

Daina / support@eazybi.com

[Ed Richard]

Thanks again Daina... it all worked out great... used Format() command with || ... will make final tweaks here and there but now have this which works...

Format(CoalesceEmpty([Measures].[Story Points Roll Up created]) * Val(ExtractString([Issue].CurrentHierarchyMember.get('Percent Done'),'(\d+)%',1)) /100,"##")||" / "||Format(Val([Measures].[Story Points Roll Up created]),"##")

[Mona Aghamirsalim]

Diana, 

I am trying to do the same thing. JWM Story Points is a numeric field. Capacity in use is text. I am not able to get this work. even without the /100 the formula didn't work for me.

1. what does the 1 in the formula mean?

2. what am I doing wrong :( ?