Problem with money/number formatting

[gshockxcc]

I have US currency formatted as '1,280.00 USD' in my data report.  I have used 'PurgeChar' to remove the ' USD' from the field.  But no matter what I do to format the values as numbers/money, I can't seem to get the correct output.

I have attached a sample file.

[robert_mika]

What output are you trying to achieve?

maybe just:

=Money#([Closed Revenue])

[#3]

Hi Kristian, some how purgechar didn't remove the extra space & causing the issue. I Tried with SubField but I am not lucky. So please use the below expression & Qlikview automatically convert into after after expression evaluation.

left( [Closed Revenue] , len([Closed Revenue])-4)

[gshockxcc]

I am trying to create a summation of the values using an Aggr function.  But when I apply that to the field, it returns nothing.  If I use "Count()", it will return the correct number of values, but Sum() will not return anything.  I assumed that this is because the field is not formatted as a number so that Qlikview can perform a summation.

When I use Money#([Closed Revenue]), Qlikview returns 1,028...

If I add another line of text below the expression, Qlikview will return 1,028.00

I don't understand what's wrong so that I can correct the formatting to do the summation.    

[jonathandienst]

Or Num(Num#(SubField([Closed Revenue], ' ', 1), '#,##0.00', '.', ','))

[lethu281]

Hi Kristan,

Have you tried to look at your default QV settings, they can possibly override your formatting, as well as the regional settings on the Server or PC that you are working from. Alternatively, you can "Trim" the data to exclude the trailing "USD".

[#7 · Accepted solution]

Purgechar is not elimanating the extra space b/n number & USD. So that causing issue. Use below expression.

left( [Closed Revenue] , len([Closed Revenue])-4)

[#8]

Somehow subfield didn't worked on this dataset.

[gshockxcc]

Agreed.  I tried the Subfield() function before submitting my post, and it did not work.  Thanks for your help.

[jonathandienst]

Because it is not a space (chr(32)), but a hard space (chr(160)) - thankyou Excel

     Num(Num#(SubField([Closed Revenue], chr(160), 1), '#,##0.00', '.', ','))