Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Displaying negetive percentage values as (##%)


Hello all,

I need to display negative percentage values in the chart in the (##%) format (as (5%), (10%) instead of -5%, -10%).

Here is the default QV formatting:

1.png

using the '#,##0%' format string

I thought displaying negative percentage values in parenthesis should be a trivial task and I've updated the chart with the following format string '#,##0%;(#,##0%)'.

As a result values are displayed like:

2.png

I am not 100% sure how QV is using this format string, but it looks like it doubles "Show in percent %" option and divides values by 100 one more time (for negative values only).

I was hoping somebody have more elegant solution instead of using (Var *100) option for negative values in my Expression....

The closest one I've found is to use the '#,##0%;(#,##0)%' format string (% sign outside the parenthesis).

3.png

This display format is a bit un-orthodox, but at least the values are correct.

Any other solutions/suggestions? I would appreciate a feedback.

Best regards,

Vladimir

12 Replies
Highlighted
Not applicable

Hi Vladimir

The following qvw, contains 4 tables:

1 -. Chart with percentage number format (simple).

2 - Table with the format #, # # 0.00%.; (#, # # 0.00)%.

3 - Table in the format expression.

4 - Table with the same number format for Actual and Budget

waiting you need this information.

DD

Highlighted
Partner
Partner

Daniel,

Thank you for suggestions.

But I really would like to avoid using IFs in Expressions....

so using num (( ( ([Actual] / [Budget]) -1)*100, '#,##0%; (#,##0%)' )  with some values conditions does not look like a elegant solution.

My main concern is why QV interprets the format string this way.

Seems like a bug to me....

Highlighted
MVP & Luminary
MVP & Luminary

Hi Vladimir,

" am not 100% sure how QV is using this format string, but it looks like it doubles "Show in percent %" option and divides values by 100 one more time (for negative values only)."

I interpret the behavior differently. To me it looks like % within the () has no effect. That is, the * 100 is not occurring, It's the raw value  that is being displayed and the % is being inserted as a literal character only.

So it looks like you can't include the % within the (). But you can put it outside or use "-nn%".

As to whether it's a bug, there is little documentation on the formatting rules but it's worth running by support.

-Rob

Highlighted
Employee
Employee

Use this formatting expression: #.##0,00%;(#.##0,00)%

Capturar.PNG.png

Highlighted
Employee
Employee

Sorry, Daniel Degollado presented same solution

Highlighted
Partner
Partner

Rob,

Thanks for reply. You probably right about the the reason QV handles this formatting string this way.

It would be actually nice if QV would allow variables or composite strings to be used in the Number formatting section.

In this case the issue might be resolved with some code like '(#,##0' & chr(37)  & ')'....

Using % sign outside the parenthesis creates a non-standard way to display a negative numbers, so I rather use a default format [-9%] instead.

Highlighted
Partner
Partner

Clever,

Yes, I know about the (9)% option, but I do not like it. It confuses people, IMHO.

I prefer to use the default format [-9%] instead.

Highlighted
MVP & Luminary
MVP & Luminary

"It would be actually nice if QV would allow variables or composite strings to be used in the Number formatting section."

Yes! I've long wanted to be able to use expressions in format property.

-Rob

Highlighted
Partner
Partner

Rob,

May be some day it will be implemented.... I've got e-mail just couple days ago that the functionality I've asked about last year was implemented in the latest SR release (GetSelectedCount and Alternative State).

So, I do believe in miracles 🙂

VK