Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Displaying Dimension for Price in a Straight Table

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?

1 Solution

Accepted Solutions
MVP
MVP

Displaying Dimension for Price in a Straight Table

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

8 Replies
MVP
MVP

Displaying Dimension for Price in a Straight Table

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

Not applicable

Displaying Dimension for Price in a Straight Table

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
Contributor III

Displaying Dimension for Price in a Straight Table

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

Not applicable

Displaying Dimension for Price in a Straight Table

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
Contributor III

Displaying Dimension for Price in a Straight Table

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.

MVP
MVP

Displaying Dimension for Price in a Straight Table

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Displaying Dimension for Price in a Straight Table

Ah yes Oracle.

I remember some issues, search the forum for

oracle number format

e.g.

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

Not applicable

Displaying Dimension for Price in a Straight Table

This works great! Thanks Jonathan!~

Num(SomeNumericField) AS SomeNumericField

Community Browser