Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Number formatting

Hi

I have been playing around with number formatting.  Our accounts dept. use a particular number format in Excel and would if possible like to replicate this in QlikView.

Negative numbers are bracketed in red, positive numbers have a trailing space (to align the decimal point) and zeros are shown as a dash.

So far I have

#,##0.00 ;(#,##0.00);-

Which works fine except the trailing space is ignored and the only way I can find to show the negative numbers in red is by using 'Visual Cues'; However, this only seems to be available to certain tables (not listboxes for example).

So Is it possible to have a trailing space on the positive number format and how do you show listbox values in red when they are negative?

Many thanks

Oli

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

The trailing space doesn't seem to be possible. Qlikview is a bit too clever for its own good here. It decides that it's a number and therefore strips any spaces when displaying the value. The space does exist as it's counted correctly in the len function. You could try another character instead of a space.

A list box can't show values in different colors. But you can use a straight table instead and format it so it looks just like a list box.


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

The trailing space doesn't seem to be possible. Qlikview is a bit too clever for its own good here. It decides that it's a number and therefore strips any spaces when displaying the value. The space does exist as it's counted correctly in the len function. You could try another character instead of a space.

A list box can't show values in different colors. But you can use a straight table instead and format it so it looks just like a list box.


talk is cheap, supply exceeds demand

View solution in original post

Contributor III
Contributor III

To get the space, you could make your field or expression into a dual.

Dual(Num(<your expression>, '#,##0;(#,##0);-') & If(<your expression> > 0, ' '), <your expression>)

Might need to wrap the text parameter in a Text() to make it include the space.

Dual(Text(Num(<your expression>, '#,##0;(#,##0);-') & If(<your expression> > 0, ' ')), <your expression>)