3 Replies Latest reply: Nov 28, 2017 4:16 AM by Juan Martinez RSS

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

    Juan Martinez

      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!