topic Problem with money/number formatting in QlikView

Problem with money/number formatting

gshockxcc — Mon, 12 Oct 2015 13:19:56 GMT

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.

Re: Problem with money/number formatting

robert_mika — Mon, 12 Oct 2015 13:25:41 GMT

What output are you trying to achieve?

maybe just:

=Money#([Closed Revenue])

Re: Problem with money/number formatting

Mon, 12 Oct 2015 13:34:48 GMT

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)

Re: Problem with money/number formatting

gshockxcc — Mon, 12 Oct 2015 13:35:11 GMT

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.

Re: Problem with money/number formatting

jonathandienst — Mon, 12 Oct 2015 13:35:41 GMT

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

Re: Problem with money/number formatting

lethu281 — Mon, 12 Oct 2015 13:36:53 GMT

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".

Re: Problem with money/number formatting

Mon, 12 Oct 2015 13:37:07 GMT

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)

Re: Problem with money/number formatting

Mon, 12 Oct 2015 13:38:18 GMT

Somehow subfield didn't worked on this dataset.

Re: Problem with money/number formatting

gshockxcc — Mon, 12 Oct 2015 13:46:32 GMT

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

Re: Problem with money/number formatting

jonathandienst — Mon, 12 Oct 2015 13:53:57 GMT

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', '.', ','))

Re: Problem with money/number formatting

gshockxcc — Mon, 12 Oct 2015 14:11:07 GMT

Now, I am intrigued. How did you find this? I found the chr(160) in my ASCII table after reading your post, "non-breaking space."

I aliased the field again and it works perfectly, same as 'dathu.qv's' suggestion.

Thanks for the tip!