topic Re: Number formatting in QlikView

Number formatting

Thu, 11 Apr 2013 09:57:18 GMT

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

Re: Number formatting

Gysbert_Wassenaar — Thu, 11 Apr 2013 17:23:01 GMT

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.

Re: Number formatting

brian554xx — Fri, 22 Jun 2018 17:19:53 GMT

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>)