Displaying Dimension for Price in a Straight Table

[#1]

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?

[swuehl · Accepted solution]

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

[#3]

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!

[jduenyas]

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

[#5]

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.

[jduenyas]

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.

[jonathandienst]

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

[swuehl]

Ah yes Oracle.

I remember some issues, search the forum for

oracle number format

e.g.

http://community.qlik.com/message/134940#134940

[#9]

This works great! Thanks Jonathan!~

Num(SomeNumericField) AS SomeNumericField