Problems working with the "money" and "money#" functions

[juanm_olondo]

Hi all,

I'm new with Qlikview and I am having troubles with the Money funcion. I have a table with a numeric field that contains numbers with up to 3 decimal digits, and I want them to have a format '0,000 €', using a coma as a decimal separator. The name of this field is 'OP'. I have tried the following things and these are the results:

     1. money(OP,'0,000 €') as OP

     2. money(OP,'#,000 €') as OP

     3. money(OP,'#,### €') as OP

     4. money#(OP,'0,000 €') as OP

     5. money#(OP,'#,000 €') as OP

     6. money#(OP,'#,### €') as OP

For all of these, I just get the same values as the ones I got without using the 'money' function, but I don't get any error message.

Then, I have tried dividing by 10:

     7. money(OP/10,'0,000 €') as OP

And with this command, it works perfectly, but this way I don't get the number I want...

Then I thought that maybe QlikView was not recognizing the field as a number, but after doing a mathematical operation it did. So, I thought of doing this:

     8. money(OP/10*10,'#,000 €') as OP

     9. money(OP/1,'#,000 €') as OP

     10. money(OP+0,'#,000 €') as OP

     11. money(OP+1-1,'#,000 €') as OP

But these commands do not work.

I tried with adding a very small quantity:

     12. money(OP+0.0000001,'#,000 €') as OP

And this works again, but since then I am making some calculations with these numbers, I end up getting small deviations from what I am supposed to obtain...

Then I tried:

     13. money(num#(OP,'0,000'),'#,000 €') as OP

And I can't understand why, this works only for some of the numbers, and I can't find a pattern to distinguish for which values it works and for which ones it doesn't. For example, it works for 1,283 but doesn't for 1,157.

I have also tried using the round function like this

     14. round(money(OP,'0,000 €'),0.001) as OP

But doesn't work at all.

And finally this:

     15. money(Round(OP,0.001),'0,000 €') as OP

That again only works for some cases.

When I say that it doesn't work I mean that the resulting format is the same one as it was before applying the 'money' function, but I am not getting any error messages. I really don't understand why it doesn't work and I am getting desperate. If someone please could help I would be very grateful.

Thanks in advance!

[m_woolf]

Is it possible that some of your OP values have leading or trailing spaces?

[el_aprendiz111]

Hi Juan,

money(num(trim(OP)),'#,000 €')

[juanm_olondo]

I'm afraid that doesn't work. I get only some of them right and the rest remains just as a number. I have tried to type the expression "money(OP,'#,000')" directly on the table where I present my results and this way it works. This is fine for me, but I am still annoyed that I couldn't do it on the script and I can't figure out why.

Thanks anyway, mw and Fer Fer, because you've taught me a new command that I'm sure will be very useful to me!