Problems working with the "money" and "money#" functions
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.
Re: Problems working with the "money" and "money#" functions
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!