topic Displaying Dimension for Price in a Straight Table in QlikView

Displaying Dimension for Price in a Straight Table

Tue, 27 Sep 2011 16:48:34 GMT

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?

Displaying Dimension for Price in a Straight Table

swuehl — Tue, 27 Sep 2011 16:57:49 GMT

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

Displaying Dimension for Price in a Straight Table

Tue, 27 Sep 2011 17:09:28 GMT

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!

Displaying Dimension for Price in a Straight Table

jduenyas — Tue, 27 Sep 2011 17:12:31 GMT

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

Displaying Dimension for Price in a Straight Table

Tue, 27 Sep 2011 17:18:54 GMT

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.

Displaying Dimension for Price in a Straight Table

jduenyas — Tue, 27 Sep 2011 17:32:56 GMT

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.

Displaying Dimension for Price in a Straight Table

jonathandienst — Tue, 27 Sep 2011 17:44:51 GMT

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

Displaying Dimension for Price in a Straight Table

swuehl — Tue, 27 Sep 2011 17:50:05 GMT

Ah yes Oracle.

I remember some issues, search the forum for

oracle number format

e.g.

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

Displaying Dimension for Price in a Straight Table

Tue, 27 Sep 2011 18:23:15 GMT

This works great! Thanks Jonathan!~

Num(SomeNumericField) AS SomeNumericField