Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a straight table with Item, Quantity and Price. I have an Expression as Sum(Price). In my straight table, the Price Dimension is showing up as 346.0000000000000000000000000000
I want to display my price as $346.00 rather than have all the additional zeros.
Under the "Numbers" tab, I can change the properties of the Expression but not the Dimensions. Can someone please help me on how to display this correctly in my straight table? Please?
Try a calculated dimension, like
=num(Price, '###.00')
But it's funny that this is not done correctly by QV.
Is your price correctly read in as number?
(If above works, I would probably use similar expression in the load rather than using a calculated dimension).
If your Price is not correctly read in as a number (maybe you have a $ in front of it?, use num#() funtion to parse it in during load).
Regards,
Stefan
Try a calculated dimension, like
=num(Price, '###.00')
But it's funny that this is not done correctly by QV.
Is your price correctly read in as number?
(If above works, I would probably use similar expression in the load rather than using a calculated dimension).
If your Price is not correctly read in as a number (maybe you have a $ in front of it?, use num#() funtion to parse it in during load).
Regards,
Stefan
Thank you for your response. I tried it, and it does work. Not sure why it is not being read in as a number.
I am not sure what you mean when you say use the num#() funciton during the load.....what would the syntax be for that?
The problem is that I also have several other dimensions that are not displaying correctly. For example, the Quantity field is displaying as 1.00000000000000000000000000000000000000000000000000000000 as well.
Suggestions?
Again, thank you!
Actually as Stefan has suggested it should work. I would only change that a little to read:
=num(sum(Price),'$#,###.00'). This will first evalute the sum and then add the thousand separator, affix the $ sign in front and add '.00' at the end.
If you wish to also show cents then use this: =num(sum(Price),'$#,###.##')
Agreed, what Stefan has suggested does work.
However, I don't know why it is not loading correctly in Qlikview because it is a numeric defined field that I'm pulling from an Oracle database. Same with Quantity, it is also defined as a number.
You may want to explore the data type (numeric, integer etc.) and precision in the database. It could be that QlikView simply complies with the data passed from Oracle. I am reading data from SQL and am not too familiar with Oracle.
Hi
This seems to an Oracle/QV "problem". I found the best way to handle it is to use
Num(SomeNumericField) AS SomeNumericField
in my load script. That takes care of the trailiing zeroes.
Regards
Jonathan
Ah yes Oracle.
I remember some issues, search the forum for
oracle number format
e.g.
This works great! Thanks Jonathan!~
Num(SomeNumericField) AS SomeNumericField