Displaying negetive percentage values as (##%)

[vlad_komarov]

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:

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:

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

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

[#2]

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

[vlad_komarov]

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

[rwunderlich]

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

[Clever_Anjos]

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

[Clever_Anjos]

Sorry, Daniel Degollado presented same solution

[vlad_komarov]

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.

[vlad_komarov]

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.

[rwunderlich]

"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

[vlad_komarov]

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